###################################################################
#           Copyright (c) 2016 by TAOS Technologies, Inc.
#                     All rights reserved.
#
#  This file is proprietary and confidential to TAOS Technologies.
#  No part of this file may be reproduced, stored, transmitted,
#  disclosed or used in any form or by any means other than as
#  expressly provided by the written permission from Jianhui Tao
#
###################################################################

# -*- coding: utf-8 -*-
from new_test_framework.utils import tdLog, tdSql, tdDnodes
import sys
import os
import taos
import time
import subprocess
import random
from faker import Faker
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
# from test_nestedQuery import *

class TestNestedQueryCase:

    def case_init(self):
        self.testcasePath = os.path.split(__file__)[0]
        self.testcaseFilename = os.path.split(__file__)[-1]
        os.system("rm -rf %s/%s.sql" % (self.testcasePath,self.testcaseFilename))

        self.num = 10
        self.fornum = 15

        self.db_nest = "nest"
        self.dropandcreateDB_random("%s" %self.db_nest, 1)

        # regular column select
        #q_select= ['ts' , '*' , 'q_int', 'q_bigint' , 'q_bigint' , 'q_smallint' , 'q_tinyint' , 'q_bool' , 'q_binary' , 'q_nchar' ,'q_float' , 'q_double' ,'q_ts ']
        self.q_select= ['q_int', 'q_bigint' , 'q_bigint' , 'q_smallint' , 'q_tinyint' , 'q_bool' , 'q_binary' , 'q_nchar' ,'q_float' , 'q_double' ,'q_ts ', 'q_int_null ', 'q_bigint_null ' , 'q_bigint_null ' , 'q_smallint_null ' , 'q_tinyint_null ' , 'q_bool_null ' , 'q_binary_null ' , 'q_nchar_null ' ,'q_float_null ' , 'q_double_null ' ,'q_ts_null ']

        # tag column select
        #t_select= ['*' , 'loc' ,'t_int', 't_bigint' , 't_bigint' , 't_smallint' , 't_tinyint' , 't_bool' , 't_binary' , 't_nchar' ,'t_float' , 't_double' ,'t_ts ']
        self.t_select= ['loc','t_int', 't_bigint' , 't_bigint' , 't_smallint' , 't_tinyint' , 't_bool' , 't_binary' , 't_nchar' ,'t_float' , 't_double' ,'t_ts ']

        # regular and tag column select
        self.qt_select=  self.q_select + self.t_select

        # distinct regular column select
        self.dq_select= ['distinct q_int', 'distinct q_bigint' , 'distinct q_smallint' , 'distinct q_tinyint' ,
                'distinct q_bool' , 'distinct q_binary' , 'distinct q_nchar' ,'distinct q_float' , 'distinct q_double' ,'distinct q_ts ']

        # distinct tag column select
        self.dt_select= ['distinct loc', 'distinct t_int', 'distinct t_bigint'  , 'distinct t_smallint' , 'distinct t_tinyint' ,
                'distinct t_bool' , 'distinct t_binary' , 'distinct t_nchar' ,'distinct t_float' , 'distinct t_double' ,'distinct t_ts ']

        # distinct regular and tag column select
        self.dqt_select= self.dq_select + self.dt_select

        # special column select
        self.s_r_select= ['_c0', '_rowts' , '_C0' ]
        self.s_s_select= ['tbname' , '_rowts' , '_c0', '_C0' ]
        self.unionall_or_union= [ ' union ' , ' union all ' ]

        # regular column where
        self.q_where = ['ts < now +1s','q_bigint >= -9223372036854775807 and q_bigint <= 9223372036854775807', 'q_int <= 2147483647 and q_int >= -2147483647',
        'q_smallint >= -32767 and q_smallint <= 32767','q_tinyint >= -127 and q_tinyint <= 127','q_float >= -1.7E308 and q_float <= 1.7E308',
        'q_double >= -1.7E308 and q_double <= 1.7E308', 'q_binary like \'binary%\'  or q_binary = \'0\' ' , 'q_nchar like \'nchar%\' or q_nchar = \'0\' ' ,
        'q_bool = true or  q_bool = false' , 'q_bool in (0 , 1)' , 'q_bool in ( true , false)' , 'q_bool = 0 or q_bool = 1',
        'q_bigint between  -9223372036854775807 and 9223372036854775807',' q_int between -2147483647 and 2147483647','q_smallint between -32767 and 32767',
        'q_bigint not between  9223372036854775807 and -9223372036854775807','q_int not between 2147483647 and -2147483647','q_smallint not between 32767 and -32767',
        'q_tinyint between -127 and 127 ','q_float >= -3.4E38  ','q_float <= 3.4E38  ','q_double >= -1.7E308  ',
        'q_double <= 1.7E308  ','q_float between -3.4E38 and 3.4E38  ','q_double between -1.7E308 and 1.7E308  ' ,'q_float not between 3.4E38 and -3.4E38  ','q_double not between 1.7E308 and -1.7E308  ',
        'q_float is not null  ' ,'q_double is not null  ' ,'q_binary match \'binary\' ','q_binary nmatch \'binarynchar\' ','q_nchar match \'nchar\' ','q_nchar nmatch \'binarynchar\' ',
        'q_binary like \'binary%\' ','(q_binary like \'binary%\'  or q_nchar = \'0\'  or q_binary = \'binary_\' ) ','q_nchar like \'nchar%\' ','(q_nchar like \'nchar%\' or q_binary = \'0\'  or q_nchar = \'nchar_\' ) ',]
        #TD-6201 ,'q_bool between 0 and 1'

        # regular column where for test union,join
        self.q_u_where = ['t1.ts < now +1s' , 't2.ts < now +1s','t1.q_bigint >= -9223372036854775807 and t1.q_bigint <= 9223372036854775807 and t2.q_bigint >= -9223372036854775807 and t2.q_bigint <= 9223372036854775807',
        't1.q_int <= 2147483647 and t1.q_int >= -2147483647 and t2.q_int <= 2147483647 and t2.q_int >= -2147483647',
        't1.q_smallint >= -32767 and t1.q_smallint <= 32767 and t2.q_smallint >= -32767 and t2.q_smallint <= 32767',
        't1.q_tinyint >= -127 and t1.q_tinyint <= 127 and t2.q_tinyint >= -127 and t2.q_tinyint <= 127',
        't1.q_float >= - 1.7E308 and t1.q_float <=  1.7E308 and t2.q_float >= - 1.7E308 and t2.q_float <=  1.7E308',
        't1.q_double >= - 1.7E308 and t1.q_double <=  1.7E308 and t2.q_double >= - 1.7E308 and t2.q_double <=  1.7E308',
        't1.q_binary like \'binary%\'  and t2.q_binary like \'binary%\'  ' ,
        't1.q_nchar like \'nchar%\' and t2.q_nchar like \'nchar%\' ' ,
        't1.q_bool in (0 , 1) and t2.q_bool in (0 , 1)' , 't1.q_bool in ( true , false) and t2.q_bool in ( true , false)' ,
        't1.q_bigint between  -9223372036854775807 and 9223372036854775807 and t2.q_bigint between  -9223372036854775807 and 9223372036854775807',
        't1.q_int between -2147483647 and 2147483647 and t2.q_int between -2147483647 and 2147483647',
        't1.q_smallint between -32767 and 32767 and t2.q_smallint between -32767 and 32767',
        't1.q_tinyint between -127 and 127 and t2.q_tinyint between -127 and 127 ','t1.q_float between -1.7E308 and  1.7E308 and t2.q_float between -1.7E308 and  1.7E308',
        't1.q_double between -1.7E308 and  1.7E308 and t2.q_double between -1.7E308 and  1.7E308',
        't1.q_bigint not between  9223372036854775807 and -9223372036854775807 and t2.q_bigint not between  9223372036854775807 and -9223372036854775807',
        't1.q_int not between 2147483647 and -2147483647 and t2.q_int not between 2147483647 and -2147483647',
        't1.q_smallint not between 32767 and -32767 and t2.q_smallint not between 32767 and -32767',
        't1.q_tinyint not between 127 and -127 and t2.q_tinyint not between 127 and -127 ','t1.q_float not between -1.7E308 and  -1.7E308 and t2.q_float not between 1.7E308 and  -1.7E308',
        't1.q_double not between 1.7E308 and  -1.7E308 and t2.q_double not between 1.7E308 and  -1.7E308']
        #TD-6201 ,'t1.q_bool between 0 and 1 or t2.q_bool between 0 and 1']
        #'t1.q_bool = true and  t1.q_bool = false and t2.q_bool = true and  t2.q_bool = false' , 't1.q_bool = 0 and t1.q_bool = 1 and t2.q_bool = 0 and t2.q_bool = 1' ,

        self.q_u_or_where = ['(t1.q_binary like \'binary%\'  or t1.q_binary = \'0\'  or t2.q_binary like \'binary%\'  or t2.q_binary = \'0\' )' ,
        '(t1.q_nchar like \'nchar%\' or t1.q_nchar = \'0\' or t2.q_nchar like \'nchar%\' or t2.q_nchar = \'0\' )' , '(t1.q_bool = true or  t1.q_bool = false or t2.q_bool = true or  t2.q_bool = false)' ,
        '(t1.q_bool in (0 , 1) or t2.q_bool in (0 , 1))' , '(t1.q_bool in ( true , false) or t2.q_bool in ( true , false))' , '(t1.q_bool = 0 or t1.q_bool = 1 or t2.q_bool = 0 or t2.q_bool = 1)' ,
        '(t1.q_bigint between  -9223372036854775807 and 9223372036854775807 or t2.q_bigint between  -9223372036854775807 and 9223372036854775807)',
        '(t1.q_int between -2147483647 and 2147483647 or t2.q_int between -2147483647 and 2147483647)',
        '(t1.q_smallint between -32767 and 32767 or t2.q_smallint between -32767 and 32767)',
        '(t1.q_tinyint between -127 and 127 or t2.q_tinyint between -127 and 127 )','(t1.q_float between -1.7E308 and 1.7E308 or t2.q_float between -1.7E308 and 1.7E308)',
        '(t1.q_double between -1.7E308 and 1.7E308 or t2.q_double between -1.7E308 and 1.7E308)']

        # tag column where
        self.t_where = ['ts < now +1s','t_bigint >= -9223372036854775807 and t_bigint <= 9223372036854775807','t_int <= 2147483647 and t_int >= -2147483647',
        't_smallint >= -32767 and t_smallint <= 32767','q_tinyint >= -127 and t_tinyint <= 127','t_float >= -1.7E308 and t_float <= 1.7E308',
        't_double >= -1.7E308 and t_double <= 1.7E308', 't_binary like \'binary%\'   or t_binary = \'0\' ' , 't_nchar like \'nchar%\' or t_nchar = \'0\'' ,
        't_bool = true or  t_bool = false' , 't_bool in (0 , 1)' , 't_bool in ( true , false)' , 't_bool = 0 or t_bool = 1',
        't_bigint between  -9223372036854775807 and 9223372036854775807',' t_int between -2147483647 and 2147483647','t_smallint between -32767 and 32767',
        't_tinyint between -127 and 127 ','t_float between -1.7E308 and 1.7E308','t_double between -1.7E308 and 1.7E308',
        't_binary match \'binary\' ','t_binary nmatch \'binarynchar\' ','t_nchar match \'nchar\' ','t_nchar nmatch \'binarynchar\' ',
        't_binary like \'binary%\' ','t_nchar like \'nchar%\' ','(t_binary like \'binary%\'  or t_nchar = \'0\' ) ','(t_nchar like \'nchar%\' or t_binary = \'0\' ) ',]
        #TD-6201,'t_bool between 0 and 1'

        # tag column where for test  union,join | this is not support
        self.t_u_where = ['t1.ts < now +1s' , 't2.ts < now +1s','t1.t_bigint >= -9223372036854775807 and t1.t_bigint <= 9223372036854775807 and t2.t_bigint >= -9223372036854775807 and t2.t_bigint <= 9223372036854775807',
        't1.t_int <= 2147483647 and t1.t_int >= -2147483647 and t2.t_int <= 2147483647 and t2.t_int >= -2147483647',
        't1.t_smallint >= -32767 and t1.t_smallint <= 32767 and t2.t_smallint >= -32767 and t2.t_smallint <= 32767',
        't1.t_tinyint >= -127 and t1.t_tinyint <= 127 and t2.t_tinyint >= -127 and t2.t_tinyint <= 127',
        't1.t_float >= -1.7E308 and t1.t_float <= 1.7E308 and t2.t_float >= -1.7E308 and t2.t_float <= 1.7E308',
        't1.t_double >= -1.7E308 and t1.t_double <= 1.7E308 and t2.t_double >= -1.7E308 and t2.t_double <= 1.7E308',
        '(t1.t_binary like \'binary%\'  or t1.t_binary = \'0\'  or t2.t_binary like \'binary%\'  or t2.t_binary = \'0\') ' ,
        '(t1.t_nchar like \'nchar%\' or t1.t_nchar = \'0\' or t2.t_nchar like \'nchar%\' or t2.t_nchar = \'0\' )' , '(t1.t_bool = true or  t1.t_bool = false or t2.t_bool = true or  t2.t_bool = false)' ,
        't1.t_bool in (0 , 1) and t2.t_bool in (0 , 1)' , 't1.t_bool in ( true , false) and t2.t_bool in ( true , false)' , '(t1.t_bool = 0 or t1.t_bool = 1 or t2.t_bool = 0 or t2.t_bool = 1)',
        't1.t_bigint between  -9223372036854775807 and 9223372036854775807 and t2.t_bigint between  -9223372036854775807 and 9223372036854775807',
        't1.t_int between -2147483647 and 2147483647 and t2.t_int between -2147483647 and 2147483647',
        't1.t_smallint between -32767 and 32767 and t2.t_smallint between -32767 and 32767',
        '(t1.t_tinyint between -127 and 127 and t2.t_tinyint between -127 and 127) ','t1.t_float between -1.7E308 and 1.7E308 and t2.t_float between -1.7E308 and 1.7E308',
        '(t1.t_double between -1.7E308 and 1.7E308 and t2.t_double between -1.7E308 and 1.7E308)']
        #TD-6201,'t1.t_bool between 0 and 1 or t2.q_bool between 0 and 1']

        self.t_u_or_where = ['(t1.t_binary like \'binary%\'  or t1.t_binary = \'0\'  or t2.t_binary like \'binary%\'  or t2.t_binary = \'0\' )' ,
        '(t1.t_nchar like \'nchar%\' or t1.t_nchar = \'0\' or t2.t_nchar like \'nchar%\' or t2.t_nchar = \'0\' )' , '(t1.t_bool = true or  t1.t_bool = false or t2.t_bool = true or  t2.t_bool = false)' ,
        '(t1.t_bool in (0 , 1) or t2.t_bool in (0 , 1))' , '(t1.t_bool in ( true , false) or t2.t_bool in ( true , false))' , '(t1.t_bool = 0 or t1.t_bool = 1 or t2.t_bool = 0 or t2.t_bool = 1)',
        '(t1.t_bigint between  -9223372036854775807 and 9223372036854775807 or t2.t_bigint between  -9223372036854775807 and 9223372036854775807)',
        '(t1.t_int between -2147483647 and 2147483647 or t2.t_int between -2147483647 and 2147483647)',
        '(t1.t_smallint between -32767 and 32767 or t2.t_smallint between -32767 and 32767)',
        '(t1.t_tinyint between -127 and 127 or t2.t_tinyint between -127 and 127 )','(t1.t_float between -1.7E308 and 1.7E308 or t2.t_float between -1.7E308 and 1.7E308)',
        '(t1.t_double between -1.7E308 and 1.7E308 or t2.t_double between -1.7E308 and 1.7E308)']

        # self.t_u_where = ['t1.ts < now +1s'] # 超级表tag不支持，暂时注掉
        # self.t_u_or_where = ['(t1.q_bool in (0 , 1))'] #超级表tag不支持，暂时注掉

        # regular and tag column where
        self.qt_where = self.q_where + self.t_where
        #self.qt_where = self.q_where #超级表tag不支持，暂时注掉
        self.qt_u_where = self.q_u_where + self.t_u_where
        # now,qt_u_or_where is not support
        self.qt_u_or_where = self.q_u_or_where + self.t_u_or_where

        # tag column where for test super join | this is  support  , 't1.t_bool = t2.t_bool ' ？？？
        self.t_join_where = ['t1.t_bigint = t2.t_bigint ', 't1.t_int = t2.t_int ', 't1.t_smallint = t2.t_smallint ', 't1.t_tinyint = t2.t_tinyint ',
                    't1.t_float = t2.t_float ', 't1.t_double = t2.t_double ', 't1.t_binary = t2.t_binary ' , 't1.t_nchar = t2.t_nchar  ' ]
        #self.t_join_where = ['t1.ts = t2.ts'] # 超级表tag不支持，暂时注掉

        # session && fill
        self.session_where = ['session(ts,10a)' , 'session(ts,10s)', 'session(ts,10m)' , 'session(ts,10h)','session(ts,10d)' , 'session(ts,10w)']
        self.session_u_where = ['session(t1.ts,10a)' , 'session(t1.ts,10s)', 'session(t1.ts,10m)' , 'session(t1.ts,10h)','session(t1.ts,10d)' , 'session(t1.ts,10w)',
                    'session(t2.ts,10a)' , 'session(t2.ts,10s)', 'session(t2.ts,10m)' , 'session(t2.ts,10h)','session(t2.ts,10d)' , 'session(t2.ts,10w)']

        self.fill_where = ['FILL(NONE)','FILL(PREV)','FILL(NULL)','FILL(LINEAR)','FILL(NEXT)','FILL(VALUE, 1.23)']

        self.state_window = ['STATE_WINDOW(q_tinyint)','STATE_WINDOW(q_bigint)','STATE_WINDOW(q_int)','STATE_WINDOW(q_bool)','STATE_WINDOW(q_smallint)']
        self.state_u_window = ['STATE_WINDOW(t1.q_tinyint)','STATE_WINDOW(t1.q_bigint)','STATE_WINDOW(t1.q_int)','STATE_WINDOW(t1.q_bool)','STATE_WINDOW(t1.q_smallint)',
                    'STATE_WINDOW(t2.q_tinyint)','STATE_WINDOW(t2.q_bigint)','STATE_WINDOW(t2.q_int)','STATE_WINDOW(t2.q_bool)','STATE_WINDOW(t2.q_smallint)']

        # order by where
        self.order_where = ['order by ts' , 'order by ts asc']
        self.order_u_where = ['order by t1.ts' , 'order by t1.ts asc' , 'order by t2.ts' , 'order by t2.ts asc']
        self.order_desc_where = ['order by ts' , 'order by ts asc' , 'order by ts desc' ]
        self.orders_desc_where = ['order by ts' , 'order by ts asc' , 'order by ts desc' , 'order by ts,loc' , 'order by ts,loc asc' , 'order by ts,loc desc']

        self.group_where = ['group by tbname , loc' , 'group by tbname', 'group by tbname, t_bigint', 'group by tbname,t_int', 'group by tbname, t_smallint', 'group by tbname,t_tinyint',
                    'group by tbname,t_float', 'group by tbname,t_double' , 'group by tbname,t_binary', 'group by tbname,t_nchar', 'group by tbname,t_bool' ,'group by tbname ,loc ,t_bigint',
                    'group by tbname,t_binary ,t_nchar ,t_bool' , 'group by tbname,t_int ,t_smallint ,t_tinyint' , 'group by tbname,t_float ,t_double ' ,
                    'PARTITION BY tbname , loc' , 'PARTITION BY tbname', 'PARTITION BY tbname, t_bigint', 'PARTITION BY tbname,t_int', 'PARTITION BY tbname, t_smallint', 'PARTITION BY tbname,t_tinyint',
                    'PARTITION BY tbname,t_float', 'PARTITION BY tbname,t_double' , 'PARTITION BY tbname,t_binary', 'PARTITION BY tbname,t_nchar', 'PARTITION BY tbname,t_bool' ,'PARTITION BY tbname ,loc ,t_bigint',
                    'PARTITION BY tbname,t_binary ,t_nchar ,t_bool' , 'PARTITION BY tbname,t_int ,t_smallint ,t_tinyint' , 'PARTITION BY tbname,t_float ,t_double ']
        self.group_where_j = ['group by  t1.loc' , 'group by t1.t_bigint', 'group by t1.t_int', 'group by t1.t_smallint', 'group by t1.t_tinyint',
                    'group by t1.t_float', 'group by t1.t_double' , 'group by t1.t_binary', 'group by t1.t_nchar', 'group by t1.t_bool' ,'group by t1.loc ,t1.t_bigint',
                    'group by t1.t_binary ,t1.t_nchar ,t1.t_bool' , 'group by t1.t_int ,t1.t_smallint ,t1.t_tinyint' , 'group by t1.t_float ,t1.t_double ' ,
                    'PARTITION BY t1.loc' , 'PARTITION by t1.t_bigint', 'PARTITION by t1.t_int', 'PARTITION by t1.t_smallint', 'PARTITION by t1.t_tinyint',
                    'PARTITION by t1.t_float', 'PARTITION by t1.t_double' , 'PARTITION by t1.t_binary', 'PARTITION by t1.t_nchar', 'PARTITION by t1.t_bool' ,'PARTITION BY t1.loc ,t1.t_bigint',
                    'PARTITION by t1.t_binary ,t1.t_nchar ,t1.t_bool' , 'PARTITION by t1.t_int ,t1.t_smallint ,t1.t_tinyint' , 'PARTITION by t1.t_float ,t1.t_double ',
                    'group by  t2.loc' , 'group by t2.t_bigint', 'group by t2.t_int', 'group by t2.t_smallint', 'group by t2.t_tinyint',
                    'group by t2.t_float', 'group by t2.t_double' , 'group by t2.t_binary', 'group by t2.t_nchar', 'group by t2.t_bool' ,'group by t2.loc ,t2.t_bigint',
                    'group by t2.t_binary ,t2.t_nchar ,t2.t_bool' , 'group by t2.t_int ,t2.t_smallint ,t2.t_tinyint' , 'group by t2.t_float ,t2.t_double ' ,
                    'PARTITION BY t2.loc' , 'PARTITION by t2.t_bigint', 'PARTITION by t2.t_int', 'PARTITION by t2.t_smallint', 'PARTITION by t2.t_tinyint',
                    'PARTITION by t2.t_float', 'PARTITION by t2.t_double' , 'PARTITION by t2.t_binary', 'PARTITION by t2.t_nchar', 'PARTITION by t2.t_bool' ,'PARTITION BY t2.loc ,t2.t_bigint',
                    'PARTITION by t2.t_binary ,t2.t_nchar ,t2.t_bool' , 'PARTITION by t2.t_int ,t2.t_smallint ,t2.t_tinyint' , 'PARTITION by t2.t_float ,t2.t_double ']

        self.group_only_where = ['group by tbname , loc' , 'group by tbname', 'group by tbname, t_bigint', 'group by tbname,t_int', 'group by tbname, t_smallint', 'group by tbname,t_tinyint',
                    'group by tbname,t_float', 'group by tbname,t_double' , 'group by tbname,t_binary', 'group by tbname,t_nchar', 'group by tbname,t_bool' ,'group by tbname ,loc ,t_bigint',
                    'group by tbname,t_binary ,t_nchar ,t_bool' , 'group by tbname,t_int ,t_smallint ,t_tinyint' , 'group by tbname,t_float ,t_double ' ]
        self.group_only_where_j = ['group by  t1.loc' , 'group by t1.t_bigint', 'group by t1.t_int', 'group by t1.t_smallint', 'group by t1.t_tinyint',
                    'group by t1.t_float', 'group by t1.t_double' , 'group by t1.t_binary', 'group by t1.t_nchar', 'group by t1.t_bool' ,'group by t1.loc ,t1.t_bigint',
                    'group by t1.t_binary ,t1.t_nchar ,t1.t_bool' , 'group by t1.t_int ,t1.t_smallint ,t1.t_tinyint' , 'group by t1.t_float ,t1.t_double ' ,
                    'group by  t2.loc' , 'group by t2.t_bigint', 'group by t2.t_int', 'group by t2.t_smallint', 'group by t2.t_tinyint',
                    'group by t2.t_float', 'group by t2.t_double' , 'group by t2.t_binary', 'group by t2.t_nchar', 'group by t2.t_bool' ,'group by t2.loc ,t2.t_bigint',
                    'group by t2.t_binary ,t2.t_nchar ,t2.t_bool' , 'group by t2.t_int ,t2.t_smallint ,t2.t_tinyint' , 'group by t2.t_float ,t2.t_double ' ]

        self.partiton_where = ['PARTITION BY tbname , loc' , 'PARTITION BY tbname', 'PARTITION BY tbname, t_bigint', 'PARTITION BY tbname,t_int', 'PARTITION BY tbname, t_smallint', 'PARTITION BY tbname,t_tinyint',
                    'PARTITION BY tbname,t_float', 'PARTITION BY tbname,t_double' , 'PARTITION BY tbname,t_binary', 'PARTITION BY tbname,t_nchar', 'PARTITION BY tbname,t_bool' ,'PARTITION BY tbname ,loc ,t_bigint',
                    'PARTITION BY tbname,t_binary ,t_nchar ,t_bool' , 'PARTITION BY tbname,t_int ,t_smallint ,t_tinyint' , 'PARTITION BY tbname,t_float ,t_double ']
        self.partiton_where_j = ['PARTITION BY t1.loc' , 'PARTITION by t1.t_bigint', 'PARTITION by t1.t_int', 'PARTITION by t1.t_smallint', 'PARTITION by t1.t_tinyint',
                    'PARTITION by t1.t_float', 'PARTITION by t1.t_double' , 'PARTITION by t1.t_binary', 'PARTITION by t1.t_nchar', 'PARTITION by t1.t_bool' ,'PARTITION BY t1.loc ,t1.t_bigint',
                    'PARTITION by t1.t_binary ,t1.t_nchar ,t1.t_bool' , 'PARTITION by t1.t_int ,t1.t_smallint ,t1.t_tinyint' , 'PARTITION by t1.t_float ,t1.t_double ',
                    'PARTITION BY t2.loc' , 'PARTITION by t2.t_bigint', 'PARTITION by t2.t_int', 'PARTITION by t2.t_smallint', 'PARTITION by t2.t_tinyint',
                    'PARTITION by t2.t_float', 'PARTITION by t2.t_double' , 'PARTITION by t2.t_binary', 'PARTITION by t2.t_nchar', 'PARTITION by t2.t_bool' ,'PARTITION BY t2.loc ,t2.t_bigint',
                    'PARTITION by t2.t_binary ,t2.t_nchar ,t2.t_bool' , 'PARTITION by t2.t_int ,t2.t_smallint ,t2.t_tinyint' , 'PARTITION by t2.t_float ,t2.t_double ']


        self.group_where_regular = ['group by tbname ' , 'group by tbname', 'group by tbname, q_bigint', 'group by tbname,q_int', 'group by tbname, q_smallint', 'group by tbname,q_tinyint',
                    'group by tbname,q_float', 'group by tbname,q_double' , 'group by tbname,q_binary', 'group by tbname,q_nchar', 'group by tbname,q_bool' ,'group by tbname ,q_bigint',
                    'group by tbname,q_binary ,q_nchar ,q_bool' , 'group by tbname,q_int ,q_smallint ,q_tinyint' , 'group by tbname,q_float ,q_double ' ,
                    'PARTITION BY tbname ' , 'PARTITION BY tbname', 'PARTITION BY tbname, q_bigint', 'PARTITION BY tbname,q_int', 'PARTITION BY tbname, q_smallint', 'PARTITION BY tbname,q_tinyint',
                    'PARTITION BY tbname,q_float', 'PARTITION BY tbname,q_double' , 'PARTITION BY tbname,q_binary', 'PARTITION BY tbname,q_nchar', 'PARTITION BY tbname,q_bool' ,'PARTITION BY tbname ,q_bigint',
                    'PARTITION BY tbname,q_binary ,q_nchar ,q_bool' , 'PARTITION BY tbname,q_int ,q_smallint ,q_tinyint' , 'PARTITION BY tbname,q_float ,q_double ']
        self.group_where_regular_j = ['group by t1.q_bigint', 'group by t1.q_int', 'group by t1.q_smallint', 'group by t1.q_tinyint',
                    'group by t1.q_float', 'group by t1.q_double' , 'group by t1.q_binary', 'group by t1.q_nchar', 'group by t1.q_bool' ,'group by t1.q_bigint',
                    'group by t1.q_binary ,t1.q_nchar ,t1.q_bool' , 'group by t1.q_int ,t1.q_smallint ,t1.q_tinyint' , 'group by t1.q_float ,t1.q_double ' ,
                    'PARTITION by t1.q_bigint', 'PARTITION by t1.q_int', 'PARTITION by t1.q_smallint', 'PARTITION by t1.q_tinyint',
                    'PARTITION by t1.q_float', 'PARTITION by t1.q_double' , 'PARTITION by t1.q_binary', 'PARTITION by t1.q_nchar', 'PARTITION by t1.q_bool' ,'PARTITION BY t1.q_bigint',
                    'PARTITION by t1.q_binary ,t1.q_nchar ,t1.q_bool' , 'PARTITION by t1.q_int ,t1.q_smallint ,t1.q_tinyint' , 'PARTITION by t1.q_float ,t1.q_double ',
                    'group by t2.q_bigint', 'group by t2.q_int', 'group by t2.q_smallint', 'group by t2.q_tinyint',
                    'group by t2.q_float', 'group by t2.q_double' , 'group by t2.q_binary', 'group by t2.q_nchar', 'group by t2.q_bool' ,'group by t2.q_bigint',
                    'group by t2.q_binary ,t2.q_nchar ,t2.q_bool' , 'group by t2.q_int ,t2.q_smallint ,t2.q_tinyint' , 'group by t2.q_float ,t2.q_double ' ,
                    'PARTITION by t2.q_bigint', 'PARTITION by t2.q_int', 'PARTITION by t2.q_smallint', 'PARTITION by t2.q_tinyint',
                    'PARTITION by t2.q_float', 'PARTITION by t2.q_double' , 'PARTITION by t2.q_binary', 'PARTITION by t2.q_nchar', 'PARTITION by t2.q_bool' ,'PARTITION BY t2.q_bigint',
                    'PARTITION by t2.q_binary ,t2.q_nchar ,t2.q_bool' , 'PARTITION by t2.q_int ,t2.q_smallint ,t2.q_tinyint' , 'PARTITION by t2.q_float ,t2.q_double ']

        self.partiton_where_regular = ['PARTITION BY tbname ' , 'PARTITION BY tbname', 'PARTITION BY tbname, q_bigint', 'PARTITION BY tbname,q_int', 'PARTITION BY tbname, q_smallint', 'PARTITION BY tbname,q_tinyint',
                    'PARTITION BY tbname,q_float', 'PARTITION BY tbname,q_double' , 'PARTITION BY tbname,q_binary', 'PARTITION BY tbname,q_nchar', 'PARTITION BY tbname,q_bool' ,'PARTITION BY tbname ,q_bigint',
                    'PARTITION BY tbname,q_binary ,q_nchar ,q_bool' , 'PARTITION BY tbname,q_int ,q_smallint ,q_tinyint' , 'PARTITION BY tbname,q_float ,q_double ']
        self.partiton_where_regular_j = ['PARTITION by t1.q_bigint', 'PARTITION by t1.q_int', 'PARTITION by t1.q_smallint', 'PARTITION by t1.q_tinyint',
                    'PARTITION by t1.q_float', 'PARTITION by t1.q_double' , 'PARTITION by t1.q_binary', 'PARTITION by t1.q_nchar', 'PARTITION by t1.q_bool' ,'PARTITION BY t1.q_bigint',
                    'PARTITION by t1.q_binary ,t1.q_nchar ,t1.q_bool' , 'PARTITION by t1.q_int ,t1.q_smallint ,t1.q_tinyint' , 'PARTITION by t1.q_float ,t1.q_double ',
                    'PARTITION by t2.q_bigint', 'PARTITION by t2.q_int', 'PARTITION by t2.q_smallint', 'PARTITION by t2.q_tinyint',
                    'PARTITION by t2.q_float', 'PARTITION by t2.q_double' , 'PARTITION by t2.q_binary', 'PARTITION by t2.q_nchar', 'PARTITION by t2.q_bool' ,'PARTITION BY t2.q_bigint',
                    'PARTITION by t2.q_binary ,t2.q_nchar ,t2.q_bool' , 'PARTITION by t2.q_int ,t2.q_smallint ,t2.q_tinyint' , 'PARTITION by t2.q_float ,t2.q_double ']

        self.having_support = ['having count(q_int) > 0','having count(q_bigint) > 0','having count(q_smallint) > 0','having count(q_tinyint) > 0','having count(q_float) > 0','having count(q_double) > 0','having count(q_bool) > 0',
                    'having avg(q_int) > 0','having avg(q_bigint) > 0','having avg(q_smallint) > 0','having avg(q_tinyint) > 0','having avg(q_float) > 0','having avg(q_double) > 0',
                    'having sum(q_int) > 0','having sum(q_bigint) > 0','having sum(q_smallint) > 0','having sum(q_tinyint) > 0','having sum(q_float) > 0','having sum(q_double) > 0',
                    'having STDDEV(q_int) > 0','having STDDEV(q_bigint) > 0','having STDDEV(q_smallint) > 0','having STDDEV(q_tinyint) > 0','having STDDEV(q_float) > 0','having STDDEV(q_double) > 0',
                    'having TWA(q_int) > 0','having  TWA(q_bigint) > 0','having  TWA(q_smallint) > 0','having  TWA(q_tinyint) > 0','having  TWA(q_float) > 0','having  TWA(q_double) > 0',
                    'having IRATE(q_int) > 0','having IRATE(q_bigint) > 0','having IRATE(q_smallint) > 0','having IRATE(q_tinyint) > 0','having IRATE(q_float) > 0','having IRATE(q_double) > 0',
                    'having MIN(q_int) > 0','having MIN(q_bigint) > 0','having MIN(q_smallint) > 0','having MIN(q_tinyint) > 0','having MIN(q_float) > 0','having MIN(q_double) > 0',
                    'having MAX(q_int) > 0','having MAX(q_bigint) > 0','having MAX(q_smallint) > 0','having MAX(q_tinyint) > 0','having MAX(q_float) > 0','having MAX(q_double) > 0',
                    'having FIRST(q_int) > 0','having FIRST(q_bigint) > 0','having FIRST(q_smallint) > 0','having FIRST(q_tinyint) > 0','having FIRST(q_float) > 0','having FIRST(q_double) > 0',
                    'having LAST(q_int) > 0','having LAST(q_bigint) > 0','having LAST(q_smallint) > 0','having LAST(q_tinyint) > 0','having LAST(q_float) > 0','having LAST(q_double) > 0',
                    'having APERCENTILE(q_int,10) > 0','having APERCENTILE(q_bigint,10) > 0','having APERCENTILE(q_smallint,10) > 0','having APERCENTILE(q_tinyint,10) > 0','having APERCENTILE(q_float,10) > 0','having APERCENTILE(q_double,10) > 0',
                    'having count(q_int_null) > 0','having count(q_bigint_null) > 0','having count(q_smallint_null) > 0','having count(q_tinyint_null) > 0','having count(q_float_null) > 0','having count(q_double_null) > 0','having count(q_bool_null) > 0',
                    'having avg(q_int_null) > 0','having avg(q_bigint_null) > 0','having avg(q_smallint_null) > 0','having avg(q_tinyint_null) > 0','having avg(q_float_null) > 0','having avg(q_double_null) > 0',
                    'having sum(q_int_null) > 0','having sum(q_bigint_null) > 0','having sum(q_smallint_null) > 0','having sum(q_tinyint_null) > 0','having sum(q_float_null) > 0','having sum(q_double_null) > 0',
                    'having STDDEV(q_int_null) > 0','having STDDEV(q_bigint_null) > 0','having STDDEV(q_smallint_null) > 0','having STDDEV(q_tinyint_null) > 0','having STDDEV(q_float_null) > 0','having STDDEV(q_double_null) > 0',
                    'having TWA(q_int_null) > 0','having  TWA(q_bigint_null) > 0','having  TWA(q_smallint_null) > 0','having  TWA(q_tinyint_null) > 0','having  TWA(q_float_null) > 0','having  TWA(q_double_null) > 0',
                    'having IRATE(q_int_null) > 0','having IRATE(q_bigint_null) > 0','having IRATE(q_smallint_null) > 0','having IRATE(q_tinyint_null) > 0','having IRATE(q_float_null) > 0','having IRATE(q_double_null) > 0',
                    'having MIN(q_int_null) > 0','having MIN(q_bigint_null) > 0','having MIN(q_smallint_null) > 0','having MIN(q_tinyint_null) > 0','having MIN(q_float_null) > 0','having MIN(q_double_null) > 0',
                    'having MAX(q_int_null) > 0','having MAX(q_bigint_null) > 0','having MAX(q_smallint_null) > 0','having MAX(q_tinyint_null) > 0','having MAX(q_float_null) > 0','having MAX(q_double_null) > 0',
                    'having FIRST(q_int_null) > 0','having FIRST(q_bigint_null) > 0','having FIRST(q_smallint_null) > 0','having FIRST(q_tinyint_null) > 0','having FIRST(q_float_null) > 0','having FIRST(q_double_null) > 0',
                    'having LAST(q_int_null) > 0','having LAST(q_bigint_null) > 0','having LAST(q_smallint_null) > 0','having LAST(q_tinyint_null) > 0','having LAST(q_float_null) > 0','having LAST(q_double_null) > 0',
                    'having APERCENTILE(q_int_null,10) > 0','having APERCENTILE(q_bigint_null,10) > 0','having APERCENTILE(q_smallint_null,10) > 0','having APERCENTILE(q_tinyint_null,10) > 0','having APERCENTILE(q_float_null,10) > 0','having APERCENTILE(q_double_null,10) > 0']
        self.having_not_support = ['having TOP(q_int,10) > 0','having TOP(q_bigint,10) > 0','having TOP(q_smallint,10) > 0','having TOP(q_tinyint,10) > 0','having TOP(q_float,10) > 0','having TOP(q_double,10) > 0','having TOP(q_bool,10) > 0',
                    'having BOTTOM(q_int,10) > 0','having BOTTOM(q_bigint,10) > 0','having BOTTOM(q_smallint,10) > 0','having BOTTOM(q_tinyint,10) > 0','having BOTTOM(q_float,10) > 0','having BOTTOM(q_double,10) > 0','having BOTTOM(q_bool,10) > 0',
                    'having LEASTSQUARES(q_int) > 0','having  LEASTSQUARES(q_bigint) > 0','having  LEASTSQUARES(q_smallint) > 0','having  LEASTSQUARES(q_tinyint) > 0','having  LEASTSQUARES(q_float) > 0','having  LEASTSQUARES(q_double) > 0','having  LEASTSQUARES(q_bool) > 0',
                    'having FIRST(q_bool) > 0','having IRATE(q_bool) > 0','having PERCENTILE(q_bool,10) > 0','having avg(q_bool) > 0','having LAST_ROW(q_bool) > 0','having sum(q_bool) > 0','having STDDEV(q_bool) > 0','having APERCENTILE(q_bool,10) > 0','having  TWA(q_bool) > 0','having LAST(q_bool) > 0',
                    'having PERCENTILE(q_int,10) > 0','having PERCENTILE(q_bigint,10) > 0','having PERCENTILE(q_smallint,10) > 0','having PERCENTILE(q_tinyint,10) > 0','having PERCENTILE(q_float,10) > 0','having PERCENTILE(q_double,10) > 0',
                    'having TOP(q_int_null,10) > 0','having TOP(q_bigint_null,10) > 0','having TOP(q_smallint_null,10) > 0','having TOP(q_tinyint_null,10) > 0','having TOP(q_float_null,10) > 0','having TOP(q_double_null,10) > 0','having TOP(q_bool_null,10) > 0',
                    'having BOTTOM(q_int_null,10) > 0','having BOTTOM(q_bigint_null,10) > 0','having BOTTOM(q_smallint_null,10) > 0','having BOTTOM(q_tinyint_null,10) > 0','having BOTTOM(q_float_null,10) > 0','having BOTTOM(q_double_null,10) > 0','having BOTTOM(q_bool_null,10) > 0',
                    'having LEASTSQUARES(q_int_null) > 0','having  LEASTSQUARES(q_bigint_null) > 0','having  LEASTSQUARES(q_smallint_null) > 0','having  LEASTSQUARES(q_tinyint_null) > 0','having  LEASTSQUARES(q_float_null) > 0','having  LEASTSQUARES(q_double_null) > 0','having  LEASTSQUARES(q_bool_null) > 0',
                    'having FIRST(q_bool_null) > 0','having IRATE(q_bool_null) > 0','having PERCENTILE(q_bool_null,10) > 0','having avg(q_bool_null) > 0','having LAST_ROW(q_bool_null) > 0','having sum(q_bool_null) > 0','having STDDEV(q_bool_null) > 0','having APERCENTILE(q_bool_null,10) > 0','having  TWA(q_bool_null) > 0','having LAST(q_bool_null) > 0',
                    'having PERCENTILE(q_int_null,10) > 0','having PERCENTILE(q_bigint_null,10) > 0','having PERCENTILE(q_smallint_null,10) > 0','having PERCENTILE(q_tinyint_null,10) > 0','having PERCENTILE(q_float_null,10) > 0','having PERCENTILE(q_double_null,10) > 0']
        self.having_tagnot_support = ['having LAST_ROW(q_int) > 0','having LAST_ROW(q_bigint) > 0','having LAST_ROW(q_smallint) > 0','having LAST_ROW(q_tinyint) > 0','having LAST_ROW(q_float) > 0','having LAST_ROW(q_double) > 0',
                                      'having LAST_ROW(q_int_null) > 0','having LAST_ROW(q_bigint_null) > 0','having LAST_ROW(q_smallint_null) > 0','having LAST_ROW(q_tinyint_null) > 0','having LAST_ROW(q_float_null) > 0','having LAST_ROW(q_double_null) > 0']

        self.having_support_j = ['having count(t1.q_int) > 0','having count(t1.q_bigint) > 0','having count(t1.q_smallint) > 0','having count(t1.q_tinyint) > 0','having count(t1.q_float) > 0','having count(t1.q_double) > 0','having count(t1.q_bool) > 0',
                    'having avg(t1.q_int) > 0','having avg(t1.q_bigint) > 0','having avg(t1.q_smallint) > 0','having avg(t1.q_tinyint) > 0','having avg(t1.q_float) > 0','having avg(t1.q_double) > 0',
                    'having sum(t1.q_int) > 0','having sum(t1.q_bigint) > 0','having sum(t1.q_smallint) > 0','having sum(t1.q_tinyint) > 0','having sum(t1.q_float) > 0','having sum(t1.q_double) > 0',
                    'having STDDEV(t1.q_int) > 0','having STDDEV(t1.q_bigint) > 0','having STDDEV(t1.q_smallint) > 0','having STDDEV(t1.q_tinyint) > 0','having STDDEV(t1.q_float) > 0','having STDDEV(t1.q_double) > 0',
                    'having TWA(t1.q_int) > 0','having  TWA(t1.q_bigint) > 0','having  TWA(t1.q_smallint) > 0','having  TWA(t1.q_tinyint) > 0','having  TWA(t1.q_float) > 0','having  TWA(t1.q_double) > 0',
                    'having IRATE(t1.q_int) > 0','having IRATE(t1.q_bigint) > 0','having IRATE(t1.q_smallint) > 0','having IRATE(t1.q_tinyint) > 0','having IRATE(t1.q_float) > 0','having IRATE(t1.q_double) > 0',
                    'having MIN(t1.q_int) > 0','having MIN(t1.q_bigint) > 0','having MIN(t1.q_smallint) > 0','having MIN(t1.q_tinyint) > 0','having MIN(t1.q_float) > 0','having MIN(t1.q_double) > 0',
                    'having MAX(t1.q_int) > 0','having MAX(t1.q_bigint) > 0','having MAX(t1.q_smallint) > 0','having MAX(t1.q_tinyint) > 0','having MAX(t1.q_float) > 0','having MAX(t1.q_double) > 0',
                    'having FIRST(t1.q_int) > 0','having FIRST(t1.q_bigint) > 0','having FIRST(t1.q_smallint) > 0','having FIRST(t1.q_tinyint) > 0','having FIRST(t1.q_float) > 0','having FIRST(t1.q_double) > 0',
                    'having LAST(t1.q_int) > 0','having LAST(t1.q_bigint) > 0','having LAST(t1.q_smallint) > 0','having LAST(t1.q_tinyint) > 0','having LAST(t1.q_float) > 0','having LAST(t1.q_double) > 0',
                    'having APERCENTILE(t1.q_int,10) > 0','having APERCENTILE(t1.q_bigint,10) > 0','having APERCENTILE(t1.q_smallint,10) > 0','having APERCENTILE(t1.q_tinyint,10) > 0','having APERCENTILE(t1.q_float,10) > 0','having APERCENTILE(t1.q_double,10) > 0']

        # limit offset where
        self.limit_where = ['limit 1 offset 1' , 'limit 1' , 'limit 2 offset 1' , 'limit 2', 'limit 12 offset 1' , 'limit 20', 'limit 20 offset 10' , 'limit 200']
        self.limit1_where = ['limit 1 offset 1' , 'limit 1' ]
        self.limit_u_where = ['limit 100 offset 10' , 'limit 50' , 'limit 100' , 'limit 10' ]

        # slimit soffset where
        self.slimit_where = ['slimit 1 soffset 1' , 'slimit 1' , 'slimit 2 soffset 1' , 'slimit 2']
        self.slimit1_where = ['slimit 2 soffset 1' , 'slimit 1' ]

        # aggregate function include [all:count(*)\avg\sum\stddev ||regualr:twa\irate\leastsquares ||group by tbname:twa\irate\]
        # select function include [all: min\max\first(*)\last(*)\top\bottom\apercentile\last_row(*)(not with interval)\interp(*)(FILL) ||regualr: percentile]
        # calculation function include [all:spread\+-*/ ||regualr:diff\derivative ||group by tbname:diff\derivative\]
        # **_ns_**  express is not support stable, therefore, separated from regular tables
        # calc_select_all   calc_select_regular  calc_select_in_ts  calc_select_fill  calc_select_not_interval
        # calc_aggregate_all   calc_aggregate_regular   calc_aggregate_groupbytbname
        # calc_calculate_all   calc_calculate_regular   calc_calculate_groupbytbname

        # calc_select_all   calc_select_regular  calc_select_in_ts  calc_select_fill  calc_select_not_interval
        # select function include [all: min\max\first(*)\last(*)\top\bottom\apercentile\last_row(*)(not with interval)\interp(*)(FILL) ||regualr: percentile]

        self.calc_select_all = ['bottom(q_int,20)' , 'bottom(q_bigint,20)' , 'bottom(q_smallint,20)' , 'bottom(q_tinyint,20)' ,'bottom(q_float,20)' , 'bottom(q_double,20)' ,
                    'top(q_int,20)' , 'top(q_bigint,20)' , 'top(q_smallint,20)' ,'top(q_tinyint,20)' ,'top(q_float,20)' ,'top(q_double,20)' ,
                    'first(q_int)' , 'first(q_bigint)' , 'first(q_smallint)' , 'first(q_tinyint)' , 'first(q_float)' ,'first(q_double)' ,'first(q_binary)' ,'first(q_nchar)' ,'first(q_bool)' ,'first(q_ts)' ,
                    'last(q_int)' ,  'last(q_bigint)' , 'last(q_smallint)'  , 'last(q_tinyint)' , 'last(q_float)'  ,'last(q_double)' , 'last(q_binary)' ,'last(q_nchar)' ,'last(q_bool)' ,'last(q_ts)' ,
                    'min(q_int)' , 'min(q_bigint)' , 'min(q_smallint)' , 'min(q_tinyint)' , 'min(q_float)' ,'min(q_double)' ,
                    'max(q_int)' ,  'max(q_bigint)' , 'max(q_smallint)' , 'max(q_tinyint)' ,'max(q_float)' ,'max(q_double)' ,
                    'apercentile(q_int,20)' ,  'apercentile(q_bigint,20)'  ,'apercentile(q_smallint,20)'  ,'apercentile(q_tinyint,20)' ,'apercentile(q_float,20)'  ,'apercentile(q_double,20)' ,
                    'last_row(q_int)' ,  'last_row(q_bigint)' , 'last_row(q_smallint)' , 'last_row(q_tinyint)' , 'last_row(q_float)' ,
                    'last_row(q_double)' , 'last_row(q_bool)' ,'last_row(q_binary)' ,'last_row(q_nchar)' ,'last_row(q_ts)',
                    'bottom(q_int_null,20)' , 'bottom(q_bigint_null,20)' , 'bottom(q_smallint_null,20)' , 'bottom(q_tinyint_null,20)' ,'bottom(q_float_null,20)' , 'bottom(q_double_null,20)' ,
                    'top(q_int_null,20)' , 'top(q_bigint_null,20)' , 'top(q_smallint_null,20)' ,'top(q_tinyint_null,20)' ,'top(q_float_null,20)' ,'top(q_double_null,20)' ,
                    'first(q_int_null)' , 'first(q_bigint_null)' , 'first(q_smallint_null)' , 'first(q_tinyint_null)' , 'first(q_float_null)' ,'first(q_double_null)' ,'first(q_binary_null)' ,'first(q_nchar_null)' ,'first(q_bool_null)' ,'first(q_ts_null)' ,
                    'last(q_int_null)' ,  'last(q_bigint_null)' , 'last(q_smallint_null)'  , 'last(q_tinyint_null)' , 'last(q_float_null)'  ,'last(q_double_null)' , 'last(q_binary_null)' ,'last(q_nchar_null)' ,'last(q_bool_null)' ,'last(q_ts_null)' ,
                    'min(q_int_null)' , 'min(q_bigint_null)' , 'min(q_smallint_null)' , 'min(q_tinyint_null)' , 'min(q_float_null)' ,'min(q_double_null)' ,
                    'max(q_int_null)' ,  'max(q_bigint_null)' , 'max(q_smallint_null)' , 'max(q_tinyint_null)' ,'max(q_float_null)' ,'max(q_double_null)' ,
                    'last_row(q_int_null)' ,  'last_row(q_bigint_null)' , 'last_row(q_smallint_null)' , 'last_row(q_tinyint_null)' , 'last_row(q_float_null)' ,
                    'last_row(q_double_null)' , 'last_row(q_bool_null)' ,'last_row(q_binary_null)' ,'last_row(q_nchar_null)' ,'last_row(q_ts_null)',
                    'apercentile(q_int_null,20)' ,  'apercentile(q_bigint_null,20)'  ,'apercentile(q_smallint_null,20)'  ,'apercentile(q_tinyint_null,20)' ,'apercentile(q_float_null,20)'  ,'apercentile(q_double_null,20)' ,]

        self.calc_select_in_ts = ['bottom(q_int,20)' , 'bottom(q_bigint,20)' , 'bottom(q_smallint,20)' , 'bottom(q_tinyint,20)' ,'bottom(q_float,20)' , 'bottom(q_double,20)' ,
                    'top(q_int,20)' , 'top(q_bigint,20)' , 'top(q_smallint,20)' ,'top(q_tinyint,20)' ,'top(q_float,20)' ,'top(q_double,20)' ,
                    'bottom(q_int_null,20)' , 'bottom(q_bigint_null,20)' , 'bottom(q_smallint_null,20)' , 'bottom(q_tinyint_null,20)' ,'bottom(q_float_null,20)' , 'bottom(q_double_null,20)' ,
                    'top(q_int_null,20)' , 'top(q_bigint_null,20)' , 'top(q_smallint_null,20)' ,'top(q_tinyint_null,20)' ,'top(q_float_null,20)' ,'top(q_double_null,20)' ,
                    'first(q_int)' , 'first(q_bigint)' , 'first(q_smallint)' , 'first(q_tinyint)' , 'first(q_float)' ,'first(q_double)' ,'first(q_binary)' ,'first(q_nchar)' ,'first(q_bool)' ,'first(q_ts)' ,
                    'last(q_int)' ,  'last(q_bigint)' , 'last(q_smallint)'  , 'last(q_tinyint)' , 'last(q_float)'  ,'last(q_double)' , 'last(q_binary)' ,'last(q_nchar)' ,'last(q_bool)' ,'last(q_ts)' ,
                    'first(q_int_null)' , 'first(q_bigint_null)' , 'first(q_smallint_null)' , 'first(q_tinyint_null)' , 'first(q_float_null)' ,'first(q_double_null)' ,'first(q_binary_null)' ,'first(q_nchar_null)' ,'first(q_bool_null)' ,'first(q_ts_null)' ,
                    'last(q_int_null)' ,  'last(q_bigint_null)' , 'last(q_smallint_null)'  , 'last(q_tinyint_null)' , 'last(q_float_null)'  ,'last(q_double_null)' , 'last(q_binary_null)' ,'last(q_nchar_null)' ,'last(q_bool_null)' ,'last(q_ts_null)' ]

        self.calc_select_in = ['min(q_int)' , 'min(q_bigint)' , 'min(q_smallint)' , 'min(q_tinyint)' , 'min(q_float)' ,'min(q_double)' ,
                    'max(q_int)' ,  'max(q_bigint)' , 'max(q_smallint)' , 'max(q_tinyint)' ,'max(q_float)' ,'max(q_double)' ,
                    'apercentile(q_int,20)' ,  'apercentile(q_bigint,20)'  ,'apercentile(q_smallint,20)'  ,'apercentile(q_tinyint,20)' ,'apercentile(q_float,20)'  ,'apercentile(q_double,20)' ,
                    'last_row(q_int)' ,  'last_row(q_bigint)' , 'last_row(q_smallint)' , 'last_row(q_tinyint)' , 'last_row(q_float)' ,
                    'last_row(q_double)' , 'last_row(q_bool)' ,'last_row(q_binary)' ,'last_row(q_nchar)' ,'last_row(q_ts)',
                    'min(q_int_null)' , 'min(q_bigint_null)' , 'min(q_smallint_null)' , 'min(q_tinyint_null)' , 'min(q_float_null)' ,'min(q_double_null)' ,
                    'max(q_int_null)' ,  'max(q_bigint_null)' , 'max(q_smallint_null)' , 'max(q_tinyint_null)' ,'max(q_float_null)' ,'max(q_double_null)' ,
                    'apercentile(q_int_null,20)' ,  'apercentile(q_bigint_null,20)'  ,'apercentile(q_smallint_null,20)'  ,'apercentile(q_tinyint_null,20)' ,'apercentile(q_float_null,20)'  ,'apercentile(q_double_null,20)' ,
                    'last_row(q_int_null)' ,  'last_row(q_bigint_null)' , 'last_row(q_smallint_null)' , 'last_row(q_tinyint_null)' , 'last_row(q_float_null)' ,
                    'last_row(q_double_null)' , 'last_row(q_bool_null)' ,'last_row(q_binary_null)' ,'last_row(q_nchar_null)' ,'last_row(q_ts_null)']

        self.calc_select_not_support_ts = ['first(q_int)' , 'first(q_bigint)' , 'first(q_smallint)' , 'first(q_tinyint)' , 'first(q_float)' ,'first(q_double)' ,'first(q_binary)' ,'first(q_nchar)' ,'first(q_bool)' ,'first(q_ts)' ,
                    'last(q_int)' ,  'last(q_bigint)' , 'last(q_smallint)'  , 'last(q_tinyint)' , 'last(q_float)'  ,'last(q_double)' , 'last(q_binary)' ,'last(q_nchar)' ,'last(q_bool)' ,'last(q_ts)' ,
                    'last_row(q_int)' ,  'last_row(q_bigint)' , 'last_row(q_smallint)' , 'last_row(q_tinyint)' , 'last_row(q_float)' ,
                    'last_row(q_double)' , 'last_row(q_bool)' ,'last_row(q_binary)' ,'last_row(q_nchar)' ,'last_row(q_ts)',
                    'apercentile(q_int,20)' ,  'apercentile(q_bigint,20)'  ,'apercentile(q_smallint,20)'  ,'apercentile(q_tinyint,20)' ,'apercentile(q_float,20)'  ,'apercentile(q_double,20)',
                    'first(q_int_null)' , 'first(q_bigint_null)' , 'first(q_smallint_null)' , 'first(q_tinyint_null)' , 'first(q_float_null)' ,'first(q_double_null)' ,'first(q_binary_null)' ,'first(q_nchar_null)' ,'first(q_bool_null)' ,'first(q_ts_null)' ,
                    'last(q_int_null)' ,  'last(q_bigint_null)' , 'last(q_smallint_null)'  , 'last(q_tinyint_null)' , 'last(q_float_null)'  ,'last(q_double_null)' , 'last(q_binary_null)' ,'last(q_nchar_null)' ,'last(q_bool_null)' ,'last(q_ts_null)' ,
                    'last_row(q_int_null)' ,  'last_row(q_bigint_null)' , 'last_row(q_smallint_null)' , 'last_row(q_tinyint_null)' , 'last_row(q_float_null)' ,
                    'last_row(q_double_null)' , 'last_row(q_bool_null)' ,'last_row(q_binary_null)' ,'last_row(q_nchar_null)' ,'last_row(q_ts_null)',
                    'apercentile(q_int_null,20)' ,  'apercentile(q_bigint_null,20)'  ,'apercentile(q_smallint_null,20)'  ,'apercentile(q_tinyint_null,20)' ,'apercentile(q_float_null,20)'  ,'apercentile(q_double_null,20)']

        self.calc_select_support_ts = ['bottom(q_int,20)' , 'bottom(q_bigint,20)' , 'bottom(q_smallint,20)' , 'bottom(q_tinyint,20)' ,'bottom(q_float,20)' , 'bottom(q_double,20)' ,
                    'top(q_int,20)' , 'top(q_bigint,20)' , 'top(q_smallint,20)' ,'top(q_tinyint,20)' ,'top(q_float,20)' ,'top(q_double,20)' ,
                    'bottom(q_int_null,20)' , 'bottom(q_bigint_null,20)' , 'bottom(q_smallint_null,20)' , 'bottom(q_tinyint_null,20)' ,'bottom(q_float_null,20)' , 'bottom(q_double_null,20)' ,
                    'top(q_int_null,20)' , 'top(q_bigint_null,20)' , 'top(q_smallint_null,20)' ,'top(q_tinyint_null,20)' ,'top(q_float_null,20)' ,'top(q_double_null,20)' ,
                    'min(q_int)' , 'min(q_bigint)' , 'min(q_smallint)' , 'min(q_tinyint)' , 'min(q_float)' ,'min(q_double)' ,
                    'max(q_int)' ,  'max(q_bigint)' , 'max(q_smallint)' , 'max(q_tinyint)' ,'max(q_float)' ,'max(q_double)'  ,
                    'min(q_int_null)' , 'min(q_bigint_null)' , 'min(q_smallint_null)' , 'min(q_tinyint_null)' , 'min(q_float_null)' ,'min(q_double_null)' ,
                    'max(q_int_null)' ,  'max(q_bigint_null)' , 'max(q_smallint_null)' , 'max(q_tinyint_null)' ,'max(q_float_null)' ,'max(q_double_null)']

        self.calc_select_regular = [ 'PERCENTILE(q_int,10)' ,'PERCENTILE(q_bigint,20)' , 'PERCENTILE(q_smallint,30)' ,'PERCENTILE(q_tinyint,40)' ,'PERCENTILE(q_float,50)' ,'PERCENTILE(q_double,60)',
                                    'PERCENTILE(q_int_null,10)' ,'PERCENTILE(q_bigint_null,20)' , 'PERCENTILE(q_smallint_null,30)' ,'PERCENTILE(q_tinyint_null,40)' ,'PERCENTILE(q_float_null,50)' ,'PERCENTILE(q_double_null,60)']


        self.calc_select_fill = ['INTERP(q_int)' ,'INTERP(q_bigint)' ,'INTERP(q_smallint)' ,'INTERP(q_tinyint)', 'INTERP(q_float)' ,'INTERP(q_double)']
        self.interp_where = ['ts = now' , 'ts = \'2020-09-13 20:26:40.000\'' , 'ts = \'2020-09-13 20:26:40.009\'' ,'tbname in (\'table_1\') and ts = now' ,'tbname in (\'table_0\' ,\'table_1\',\'table_2\',\'table_3\',\'table_4\',\'table_5\') and ts =  \'2020-09-13 20:26:40.000\'','tbname like \'table%\'  and ts =  \'2020-09-13 20:26:40.002\'']

        #two table join
        self.calc_select_in_ts_j = ['bottom(t1.q_int,20)' , 'bottom(t1.q_bigint,20)' , 'bottom(t1.q_smallint,20)' , 'bottom(t1.q_tinyint,20)' ,'bottom(t1.q_float,20)' , 'bottom(t1.q_double,20)' ,
                    'top(t1.q_int,20)' , 'top(t1.q_bigint,20)' , 'top(t1.q_smallint,20)' ,'top(t1.q_tinyint,20)' ,'top(t1.q_float,20)' ,'top(t1.q_double,20)' ,
                    'first(t1.q_int)' , 'first(t1.q_bigint)' , 'first(t1.q_smallint)' , 'first(t1.q_tinyint)' , 'first(t1.q_float)' ,'first(t1.q_double)' ,'first(t1.q_binary)' ,'first(t1.q_nchar)' ,'first(t1.q_bool)' ,'first(t1.q_ts)' ,
                    'last(t1.q_int)' ,  'last(t1.q_bigint)' , 'last(t1.q_smallint)'  , 'last(t1.q_tinyint)' , 'last(t1.q_float)'  ,'last(t1.q_double)' , 'last(t1.q_binary)' ,'last(t1.q_nchar)' ,'last(t1.q_bool)' ,'last(t1.q_ts)' ,
                    'bottom(t2.q_int,20)' , 'bottom(t2.q_bigint,20)' , 'bottom(t2.q_smallint,20)' , 'bottom(t2.q_tinyint,20)' ,'bottom(t2.q_float,20)' , 'bottom(t2.q_double,20)' ,
                    'top(t2.q_int,20)' , 'top(t2.q_bigint,20)' , 'top(t2.q_smallint,20)' ,'top(t2.q_tinyint,20)' ,'top(t2.q_float,20)' ,'top(t2.q_double,20)' ,
                    'first(t2.q_int)' , 'first(t2.q_bigint)' , 'first(t2.q_smallint)' , 'first(t2.q_tinyint)' , 'first(t2.q_float)' ,'first(t2.q_double)' ,'first(t2.q_binary)' ,'first(t2.q_nchar)' ,'first(t2.q_bool)' ,'first(t2.q_ts)' ,
                    'last(t2.q_int)' ,  'last(t2.q_bigint)' , 'last(t2.q_smallint)'  , 'last(t2.q_tinyint)' , 'last(t2.q_float)'  ,'last(t2.q_double)' , 'last(t2.q_binary)' ,'last(t2.q_nchar)' ,'last(t2.q_bool)' ,'last(t2.q_ts)',
                    'bottom(t1.q_int_null,20)' , 'bottom(t1.q_bigint_null,20)' , 'bottom(t1.q_smallint_null,20)' , 'bottom(t1.q_tinyint_null,20)' ,'bottom(t1.q_float_null,20)' , 'bottom(t1.q_double_null,20)' ,
                    'top(t1.q_int_null,20)' , 'top(t1.q_bigint_null,20)' , 'top(t1.q_smallint_null,20)' ,'top(t1.q_tinyint_null,20)' ,'top(t1.q_float_null,20)' ,'top(t1.q_double_null,20)' ,
                    'first(t1.q_int_null)' , 'first(t1.q_bigint_null)' , 'first(t1.q_smallint_null)' , 'first(t1.q_tinyint_null)' , 'first(t1.q_float_null)' ,'first(t1.q_double_null)' ,'first(t1.q_binary_null)' ,'first(t1.q_nchar_null))' ,'first(t1.q_bool_null)' ,'first(t1.q_ts_null)' ,
                    'last(t1.q_int_null)' ,  'last(t1.q_bigint_null)' , 'last(t1.q_smallint_null)'  , 'last(t1.q_tinyint_null)' , 'last(t1.q_float_null)'  ,'last(t1.q_double_null)' , 'last(t1.q_binary_null)' ,'last(t1.q_nchar_null))' ,'last(t1.q_bool_null)' ,'last(t1.q_ts_null)' ,
                    'bottom(t2.q_int_null,20)' , 'bottom(t2.q_bigint_null,20)' , 'bottom(t2.q_smallint_null,20)' , 'bottom(t2.q_tinyint_null,20)' ,'bottom(t2.q_float_null,20)' , 'bottom(t2.q_double_null,20)' ,
                    'top(t2.q_int_null,20)' , 'top(t2.q_bigint_null,20)' , 'top(t2.q_smallint_null,20)' ,'top(t2.q_tinyint_null,20)' ,'top(t2.q_float_null,20)' ,'top(t2.q_double_null,20)' ,
                    'first(t2.q_int_null)' , 'first(t2.q_bigint_null)' , 'first(t2.q_smallint_null)' , 'first(t2.q_tinyint_null)' , 'first(t2.q_float_null)' ,'first(t2.q_double_null)' ,'first(t2.q_binary_null)' ,'first(t2.q_nchar_null))' ,'first(t2.q_bool_null)' ,'first(t2.q_ts_null)' ,
                    'last(t2.q_int_null)' ,  'last(t2.q_bigint_null)' , 'last(t2.q_smallint_null)'  , 'last(t2.q_tinyint_null)' , 'last(t2.q_float_null)'  ,'last(t2.q_double_null)' , 'last(t2.q_binary_null)' ,'last(t2.q_nchar_null))' ,'last(t2.q_bool_null)' ,'last(t2.q_ts_null)']

        self.calc_select_in_support_ts_j = ['bottom(t1.q_int,20)' , 'bottom(t1.q_bigint,20)' , 'bottom(t1.q_smallint,20)' , 'bottom(t1.q_tinyint,20)' ,'bottom(t1.q_float,20)' , 'bottom(t1.q_double,20)' ,
                    'top(t1.q_int,20)' , 'top(t1.q_bigint,20)' , 'top(t1.q_smallint,20)' ,'top(t1.q_tinyint,20)' ,'top(t1.q_float,20)' ,'top(t1.q_double,20)' ,
                    'min(t1.q_int)' , 'min(t1.q_bigint)' , 'min(t1.q_smallint)' , 'min(t1.q_tinyint)' , 'min(t1.q_float)' ,'min(t1.q_double)' ,
                    'max(t1.q_int)' ,  'max(t1.q_bigint)' , 'max(t1.q_smallint)' , 'max(t1.q_tinyint)' ,'max(t1.q_float)' ,'max(t1.q_double)' ,
                    'bottom(t2.q_int,20)' , 'bottom(t2.q_bigint,20)' , 'bottom(t2.q_smallint,20)' , 'bottom(t2.q_tinyint,20)' ,'bottom(t2.q_float,20)' , 'bottom(t2.q_double,20)' ,
                    'top(t2.q_int,20)' , 'top(t2.q_bigint,20)' , 'top(t2.q_smallint,20)' ,'top(t2.q_tinyint,20)' ,'top(t2.q_float,20)' ,'top(t2.q_double,20)' ,
                    'min(t2.q_int)' , 'min(t2.q_bigint)' , 'min(t2.q_smallint)' , 'min(t2.q_tinyint)' , 'min(t2.q_float)' ,'min(t2.q_double)' ,
                    'max(t2.q_int)' ,  'max(t2.q_bigint)' , 'max(t2.q_smallint)' , 'max(t2.q_tinyint)' ,'max(t2.q_float)' ,'max(t2.q_double)' ,
                    'bottom(t1.q_int_null,20)' , 'bottom(t1.q_bigint_null,20)' , 'bottom(t1.q_smallint_null,20)' , 'bottom(t1.q_tinyint_null,20)' ,'bottom(t1.q_float_null,20)' , 'bottom(t1.q_double_null,20)' ,
                    'top(t1.q_int_null,20)' , 'top(t1.q_bigint_null,20)' , 'top(t1.q_smallint_null,20)' ,'top(t1.q_tinyint_null,20)' ,'top(t1.q_float_null,20)' ,'top(t1.q_double_null,20)' ,
                    'bottom(t2.q_int_null,20)' , 'bottom(t2.q_bigint_null,20)' , 'bottom(t2.q_smallint_null,20)' , 'bottom(t2.q_tinyint_null,20)' ,'bottom(t2.q_float_null,20)' , 'bottom(t2.q_double_null,20)' ,
                    'top(t2.q_int_null,20)' , 'top(t2.q_bigint_null,20)' , 'top(t2.q_smallint_null,20)' ,'top(t2.q_tinyint_null,20)' ,'top(t2.q_float_null,20)' ,'top(t2.q_double_null,20)' ,
                    'min(t1.q_int_null)' , 'min(t1.q_bigint_null)' , 'min(t1.q_smallint_null)' , 'min(t1.q_tinyint_null)' , 'min(t1.q_float_null)' ,'min(t1.q_double_null)' ,
                    'max(t1.q_int_null)' ,  'max(t1.q_bigint_null)' , 'max(t1.q_smallint_null)' , 'max(t1.q_tinyint_null)' ,'max(t1.q_float_null)' ,'max(t1.q_double_null)' ,
                    'min(t2.q_int_null)' , 'min(t2.q_bigint_null)' , 'min(t2.q_smallint_null)' , 'min(t2.q_tinyint_null)' , 'min(t2.q_float_null)' ,'min(t2.q_double_null)' ,
                    'max(t2.q_int_null)' ,  'max(t2.q_bigint_null)' , 'max(t2.q_smallint_null)' , 'max(t2.q_tinyint_null)' ,'max(t2.q_float_null)' ,'max(t2.q_double_null)' ]

        self.calc_select_in_not_support_ts_j = ['apercentile(t1.q_int,20)' ,  'apercentile(t1.q_bigint,20)'  ,'apercentile(t1.q_smallint,20)'  ,'apercentile(t1.q_tinyint,20)' ,'apercentile(t1.q_float,20)'  ,'apercentile(t1.q_double,20)' ,
                    'apercentile(t1.q_int_null,20)' ,  'apercentile(t1.q_bigint_null,20)'  ,'apercentile(t1.q_smallint_null,20)'  ,'apercentile(t1.q_tinyint_null,20)' ,'apercentile(t1.q_float_null,20)'  ,'apercentile(t1.q_double_null,20)' ,
                    'apercentile(t2.q_int,20)' ,  'apercentile(t2.q_bigint,20)'  ,'apercentile(t2.q_smallint,20)'  ,'apercentile(t2.q_tinyint,20)' ,'apercentile(t2.q_float,20)'  ,'apercentile(t2.q_double,20)' ,
                    'apercentile(t2.q_int_null,20)' ,  'apercentile(t2.q_bigint_null,20)'  ,'apercentile(t2.q_smallint_null,20)'  ,'apercentile(t2.q_tinyint_null,20)' ,'apercentile(t2.q_float_null,20)'  ,'apercentile(t2.q_double_null,20)' ]

        self.calc_select_in_j = ['min(t1.q_int)' , 'min(t1.q_bigint)' , 'min(t1.q_smallint)' , 'min(t1.q_tinyint)' , 'min(t1.q_float)' ,'min(t1.q_double)' ,
                    'max(t1.q_int)' ,  'max(t1.q_bigint)' , 'max(t1.q_smallint)' , 'max(t1.q_tinyint)' ,'max(t1.q_float)' ,'max(t1.q_double)' ,
                    'apercentile(t1.q_int,20)' ,  'apercentile(t1.q_bigint,20)'  ,'apercentile(t1.q_smallint,20)'  ,'apercentile(t1.q_tinyint,20)' ,'apercentile(t1.q_float,20)'  ,'apercentile(t1.q_double,20)' ,
                    'min(t1.q_int_null)' , 'min(t1.q_bigint_null)' , 'min(t1.q_smallint_null)' , 'min(t1.q_tinyint_null)' , 'min(t1.q_float_null)' ,'min(t1.q_double_null)' ,
                    'max(t1.q_int_null)' ,  'max(t1.q_bigint_null)' , 'max(t1.q_smallint_null)' , 'max(t1.q_tinyint_null)' ,'max(t1.q_float_null)' ,'max(t1.q_double_null)' ,
                    'apercentile(t1.q_int_null,20)' ,  'apercentile(t1.q_bigint_null,20)'  ,'apercentile(t1.q_smallint_null,20)'  ,'apercentile(t1.q_tinyint_null,20)' ,'apercentile(t1.q_float_null,20)'  ,'apercentile(t1.q_double_null,20)' ,
                    'last_row(t1.q_int)' ,  'last_row(t1.q_bigint)' , 'last_row(t1.q_smallint)' , 'last_row(t1.q_tinyint)' , 'last_row(t1.q_float)' ,
                    'last_row(t1.q_double)' , 'last_row(t1.q_bool)' ,'last_row(t1.q_binary)' ,'last_row(t1.q_nchar)' ,'last_row(t1.q_ts)' ,
                    'min(t2.q_int)' , 'min(t2.q_bigint)' , 'min(t2.q_smallint)' , 'min(t2.q_tinyint)' , 'min(t2.q_float)' ,'min(t2.q_double)' ,
                    'max(t2.q_int)' ,  'max(t2.q_bigint)' , 'max(t2.q_smallint)' , 'max(t2.q_tinyint)' ,'max(t2.q_float)' ,'max(t2.q_double)' ,
                    'last_row(t1.q_int_null)' ,  'last_row(t1.q_bigint_null)' , 'last_row(t1.q_smallint_null)' , 'last_row(t1.q_tinyint_null)' , 'last_row(t1.q_float_null)' ,
                    'last_row(t1.q_double_null)' , 'last_row(t1.q_bool_null)' ,'last_row(t1.q_binary_null)' ,'last_row(t1.q_nchar_null)' ,'last_row(t1.q_ts_null)' ,
                    'min(t2.q_int_null)' , 'min(t2.q_bigint_null)' , 'min(t2.q_smallint_null)' , 'min(t2.q_tinyint_null)' , 'min(t2.q_float_null)' ,'min(t2.q_double_null)' ,
                    'max(t2.q_int_null)' ,  'max(t2.q_bigint_null)' , 'max(t2.q_smallint_null)' , 'max(t2.q_tinyint_null)' ,'max(t2.q_float_null)' ,'max(t2.q_double_null)' ,
                    'apercentile(t2.q_int,20)' ,  'apercentile(t2.q_bigint,20)'  ,'apercentile(t2.q_smallint,20)'  ,'apercentile(t2.q_tinyint,20)' ,'apercentile(t2.q_float,20)'  ,'apercentile(t2.q_double,20)' ,
                    'apercentile(t2.q_int_null,20)' ,  'apercentile(t2.q_bigint_null,20)'  ,'apercentile(t2.q_smallint_null,20)'  ,'apercentile(t2.q_tinyint_null,20)' ,'apercentile(t2.q_float_null,20)'  ,'apercentile(t2.q_double_null,20)' ,
                    'last_row(t2.q_int)' ,  'last_row(t2.q_bigint)' , 'last_row(t2.q_smallint)' , 'last_row(t2.q_tinyint)' , 'last_row(t2.q_float)' ,
                    'last_row(t2.q_double)' , 'last_row(t2.q_bool)' ,'last_row(t2.q_binary)' ,'last_row(t2.q_nchar)' ,'last_row(t2.q_ts)',
                    'last_row(t2.q_int_null)' ,  'last_row(t2.q_bigint_null)' , 'last_row(t2.q_smallint_null)' , 'last_row(t2.q_tinyint_null)' , 'last_row(t2.q_float_null)' ,
                    'last_row(t2.q_double_null)' , 'last_row(t2.q_bool_null)' ,'last_row(t2.q_binary_null)' ,'last_row(t2.q_nchar_null)' ,'last_row(t2.q_ts_null)']
        self.calc_select_all_j = self.calc_select_in_ts_j + self.calc_select_in_j

        self.calc_select_regular_j = [ 'PERCENTILE(t1.q_int,10)' ,'PERCENTILE(t1.q_bigint,20)' , 'PERCENTILE(t1.q_smallint,30)' ,'PERCENTILE(t1.q_tinyint,40)' ,'PERCENTILE(t1.q_float,50)' ,'PERCENTILE(t1.q_double,60)' ,
                    'PERCENTILE(t2.q_int,10)' ,'PERCENTILE(t2.q_bigint,20)' , 'PERCENTILE(t2.q_smallint,30)' ,'PERCENTILE(t2.q_tinyint,40)' ,'PERCENTILE(t2.q_float,50)' ,'PERCENTILE(t2.q_double,60)',
                    'PERCENTILE(t1.q_int_null,10)' ,'PERCENTILE(t1.q_bigint_null,20)' , 'PERCENTILE(t1.q_smallint_null,30)' ,'PERCENTILE(t1.q_tinyint_null,40)' ,'PERCENTILE(t1.q_float_null,50)' ,'PERCENTILE(t1.q_double_null,60)' ,
                    'PERCENTILE(t2.q_int_null,10)' ,'PERCENTILE(t2.q_bigint_null,20)' , 'PERCENTILE(t2.q_smallint_null,30)' ,'PERCENTILE(t2.q_tinyint_null,40)' ,'PERCENTILE(t2.q_float_null,50)' ,'PERCENTILE(t2.q_double_null,60)']


        self.calc_select_fill_j = ['INTERP(t1.q_int)' ,'INTERP(t1.q_bigint)' ,'INTERP(t1.q_smallint)' ,'INTERP(t1.q_tinyint)', 'INTERP(t1.q_float)' ,'INTERP(t1.q_double)' ,
                    'INTERP(t2.q_int)' ,'INTERP(t2.q_bigint)' ,'INTERP(t2.q_smallint)' ,'INTERP(t2.q_tinyint)', 'INTERP(t2.q_float)' ,'INTERP(t2.q_double)']
        self.interp_where_j = ['t1.ts = now' , 't1.ts = \'2020-09-13 20:26:40.000\'' , 't1.ts = \'2020-09-13 20:26:40.009\'' ,'t2.ts = now' , 't2.ts = \'2020-09-13 20:26:40.000\'' , 't2.ts = \'2020-09-13 20:26:40.009\'' ,
                    't1.tbname in (\'table_1\') and t1.ts = now' ,'t1.tbname in (\'table_0\' ,\'table_1\',\'table_2\',\'table_3\',\'table_4\',\'table_5\') and t1.ts =  \'2020-09-13 20:26:40.000\'','t1.tbname like \'table%\'  and t1.ts =  \'2020-09-13 20:26:40.002\'',
                    't2.tbname in (\'table_1\') and t2.ts = now' ,'t2.tbname in (\'table_0\' ,\'table_1\',\'table_2\',\'table_3\',\'table_4\',\'table_5\') and t2.ts =  \'2020-09-13 20:26:40.000\'','t2.tbname like \'table%\'  and t2.ts =  \'2020-09-13 20:26:40.002\'']

        # calc_aggregate_all   calc_aggregate_regular   calc_aggregate_groupbytbname  APERCENTILE\PERCENTILE
        # aggregate function include [all:count(*)\avg\sum\stddev ||regualr:twa\irate\leastsquares ||group by tbname:twa\irate\]
        self.calc_aggregate_all = ['count(*)' , 'count(q_int)' ,'count(q_bigint)' , 'count(q_smallint)' ,'count(q_tinyint)' ,'count(q_float)' ,
                    'count(q_double)' ,'count(q_binary)' ,'count(q_nchar)' ,'count(q_bool)' ,'count(q_ts)' ,
                    'avg(q_int)' ,'avg(q_bigint)' , 'avg(q_smallint)' ,'avg(q_tinyint)' ,'avg(q_float)' ,'avg(q_double)' ,
                    'sum(q_int)' ,'sum(q_bigint)' , 'sum(q_smallint)' ,'sum(q_tinyint)' ,'sum(q_float)' ,'sum(q_double)' ,
                    'STDDEV(q_int)' ,'STDDEV(q_bigint)' , 'STDDEV(q_smallint)' ,'STDDEV(q_tinyint)' ,'STDDEV(q_float)' ,'STDDEV(q_double)',
                    'APERCENTILE(q_int,10)' ,'APERCENTILE(q_bigint,20)' , 'APERCENTILE(q_smallint,30)' ,'APERCENTILE(q_tinyint,40)' ,'APERCENTILE(q_float,50)' ,'APERCENTILE(q_double,60)',
                    'count(q_int_null)' ,'count(q_bigint_null)' , 'count(q_smallint_null)' ,'count(q_tinyint_null)' ,'count(q_float_null)' ,
                    'count(q_double_null)' ,'count(q_binary_null)' ,'count(q_nchar_null)' ,'count(q_bool_null)' ,'count(q_ts_null)' ,
                    'avg(q_int_null)' ,'avg(q_bigint_null)' , 'avg(q_smallint_null)' ,'avg(q_tinyint_null)' ,'avg(q_float_null)' ,'avg(q_double_null)' ,
                    'sum(q_int_null)' ,'sum(q_bigint_null)' , 'sum(q_smallint_null)' ,'sum(q_tinyint_null)' ,'sum(q_float_null)' ,'sum(q_double_null)' ,
                    'STDDEV(q_int_null)' ,'STDDEV(q_bigint_null)' , 'STDDEV(q_smallint_null)' ,'STDDEV(q_tinyint_null)' ,'STDDEV(q_float_null)' ,'STDDEV(q_double_null)',
                    'APERCENTILE(q_int_null,10)' ,'APERCENTILE(q_bigint_null,20)' , 'APERCENTILE(q_smallint_null,30)' ,'APERCENTILE(q_tinyint_null,40)' ,'APERCENTILE(q_float_null,50)' ,'APERCENTILE(q_double_null,60)']

        self.calc_aggregate_regular = ['twa(q_int)' ,'twa(q_bigint)' , 'twa(q_smallint)' ,'twa(q_tinyint)' ,'twa (q_float)' ,'twa(q_double)' ,
                    'IRATE(q_int)' ,'IRATE(q_bigint)' , 'IRATE(q_smallint)' ,'IRATE(q_tinyint)' ,'IRATE (q_float)' ,'IRATE(q_double)' ,
                    'twa(q_int_null)' ,'twa(q_bigint_null)' , 'twa(q_smallint_null)' ,'twa(q_tinyint_null)' ,'twa (q_float_null)' ,'twa(q_double_null)' ,
                    'IRATE(q_int_null)' ,'IRATE(q_bigint_null)' , 'IRATE(q_smallint_null)' ,'IRATE(q_tinyint_null)' ,'IRATE (q_float_null)' ,'IRATE(q_double_null)' ,
                    'LEASTSQUARES(q_int,15,3)' , 'LEASTSQUARES(q_bigint,10,1)' , 'LEASTSQUARES(q_smallint,20,3)' ,'LEASTSQUARES(q_tinyint,10,4)' ,'LEASTSQUARES(q_float,6,4)' ,'LEASTSQUARES(q_double,3,1)' ,
                    'PERCENTILE(q_int,10)' ,'PERCENTILE(q_bigint,20)' , 'PERCENTILE(q_smallint,30)' ,'PERCENTILE(q_tinyint,40)' ,'PERCENTILE(q_float,50)' ,'PERCENTILE(q_double,60)',
                    'LEASTSQUARES(q_int_null,15,3)' , 'LEASTSQUARES(q_bigint_null,10,1)' , 'LEASTSQUARES(q_smallint_null,20,3)' ,'LEASTSQUARES(q_tinyint_null,10,4)' ,'LEASTSQUARES(q_float_null,6,4)' ,'LEASTSQUARES(q_double_null,3,1)' ,
                    'PERCENTILE(q_int_null,10)' ,'PERCENTILE(q_bigint_null,20)' , 'PERCENTILE(q_smallint_null,30)' ,'PERCENTILE(q_tinyint_null,40)' ,'PERCENTILE(q_float_null,50)' ,'PERCENTILE(q_double_null,60)']

        self.calc_aggregate_groupbytbname = ['twa(q_int)' ,'twa(q_bigint)' , 'twa(q_smallint)' ,'twa(q_tinyint)' ,'twa (q_float)' ,'twa(q_double)' ,
                    'IRATE(q_int)' ,'IRATE(q_bigint)' , 'IRATE(q_smallint)' ,'IRATE(q_tinyint)' ,'IRATE (q_float)' ,'IRATE(q_double)',
                    'twa(q_int_null)' ,'twa(q_bigint_null)' , 'twa(q_smallint_null)' ,'twa(q_tinyint_null)' ,'twa (q_float_null)' ,'twa(q_double_null)' ,
                    'IRATE(q_int_null)' ,'IRATE(q_bigint_null)' , 'IRATE(q_smallint_null)' ,'IRATE(q_tinyint_null)' ,'IRATE (q_float_null)' ,'IRATE(q_double_null)']

        #two table join
        self.calc_aggregate_all_j = ['count(t1.*)' , 'count(t1.q_int)' ,'count(t1.q_bigint)' , 'count(t1.q_smallint)' ,'count(t1.q_tinyint)' ,'count(t1.q_float)' ,
                    'count(t1.q_double)' ,'count(t1.q_binary)' ,'count(t1.q_nchar)' ,'count(t1.q_bool)' ,'count(t1.q_ts)' ,
                    'avg(t1.q_int)' ,'avg(t1.q_bigint)' , 'avg(t1.q_smallint)' ,'avg(t1.q_tinyint)' ,'avg(t1.q_float)' ,'avg(t1.q_double)' ,
                    'sum(t1.q_int)' ,'sum(t1.q_bigint)' , 'sum(t1.q_smallint)' ,'sum(t1.q_tinyint)' ,'sum(t1.q_float)' ,'sum(t1.q_double)' ,
                    'STDDEV(t1.q_int)' ,'STDDEV(t1.q_bigint)' , 'STDDEV(t1.q_smallint)' ,'STDDEV(t1.q_tinyint)' ,'STDDEV(t1.q_float)' ,'STDDEV(t1.q_double)',
                    'APERCENTILE(t1.q_int,10)' ,'APERCENTILE(t1.q_bigint,20)' , 'APERCENTILE(t1.q_smallint,30)' ,'APERCENTILE(t1.q_tinyint,40)' ,'APERCENTILE(t1.q_float,50)' ,'APERCENTILE(t1.q_double,60)' ,
                    'count(t1.q_int_null)' ,'count(t1.q_bigint_null)' , 'count(t1.q_smallint_null)' ,'count(t1.q_tinyint_null)' ,'count(t1.q_float_null)' ,
                    'count(t1.q_double_null)' ,'count(t1.q_binary_null)' ,'count(t1.q_nchar_null)' ,'count(t1.q_bool_null)' ,'count(t1.q_ts_null)' ,
                    'avg(t1.q_int_null)' ,'avg(t1.q_bigint_null)' , 'avg(t1.q_smallint_null)' ,'avg(t1.q_tinyint_null)' ,'avg(t1.q_float_null)' ,'avg(t1.q_double_null)' ,
                    'sum(t1.q_int_null)' ,'sum(t1.q_bigint_null)' , 'sum(t1.q_smallint_null)' ,'sum(t1.q_tinyint_null)' ,'sum(t1.q_float_null)' ,'sum(t1.q_double_null)' ,
                    'STDDEV(t1.q_int_null)' ,'STDDEV(t1.q_bigint_null)' , 'STDDEV(t1.q_smallint_null)' ,'STDDEV(t1.q_tinyint_null)' ,'STDDEV(t1.q_float_null)' ,'STDDEV(t1.q_double_null)',
                    'APERCENTILE(t1.q_int_null,10)' ,'APERCENTILE(t1.q_bigint_null,20)' , 'APERCENTILE(t1.q_smallint_null,30)' ,'APERCENTILE(t1.q_tinyint_null,40)' ,'APERCENTILE(t1.q_float_null,50)' ,'APERCENTILE(t1.q_double,60)' ,
                    'count(t2.*)' , 'count(t2.q_int)' ,'count(t2.q_bigint)' , 'count(t2.q_smallint)' ,'count(t2.q_tinyint)' ,'count(t2.q_float)' ,
                    'count(t2.q_double)' ,'count(t2.q_binary)' ,'count(t2.q_nchar)' ,'count(t2.q_bool)' ,'count(t2.q_ts)' ,
                    'avg(t2.q_int)' ,'avg(t2.q_bigint)' , 'avg(t2.q_smallint)' ,'avg(t2.q_tinyint)' ,'avg(t2.q_float)' ,'avg(t2.q_double)' ,
                    'sum(t2.q_int)' ,'sum(t2.q_bigint)' , 'sum(t2.q_smallint)' ,'sum(t2.q_tinyint)' ,'sum(t2.q_float)' ,'sum(t2.q_double)' ,
                    'STDDEV(t2.q_int)' ,'STDDEV(t2.q_bigint)' , 'STDDEV(t2.q_smallint)' ,'STDDEV(t2.q_tinyint)' ,'STDDEV(t2.q_float)' ,'STDDEV(t2.q_double)',
                    'APERCENTILE(t2.q_int,10)' ,'APERCENTILE(t2.q_bigint,20)' , 'APERCENTILE(t2.q_smallint,30)' ,'APERCENTILE(t2.q_tinyint,40)' ,'APERCENTILE(t2.q_float,50)' ,'APERCENTILE(t2.q_double,60)',
                    'count(t2.q_int_null)' ,'count(t2.q_bigint_null)' , 'count(t2.q_smallint_null)' ,'count(t2.q_tinyint_null)' ,'count(t2.q_float_null)' ,
                    'count(t2.q_double_null)' ,'count(t2.q_binary_null)' ,'count(t2.q_nchar_null)' ,'count(t2.q_bool_null)' ,'count(t2.q_ts_null)' ,
                    'avg(t2.q_int_null)' ,'avg(t2.q_bigint_null)' , 'avg(t2.q_smallint_null)' ,'avg(t2.q_tinyint_null)' ,'avg(t2.q_float_null)' ,'avg(t2.q_double_null)' ,
                    'sum(t2.q_int_null)' ,'sum(t2.q_bigint_null)' , 'sum(t2.q_smallint_null)' ,'sum(t2.q_tinyint_null)' ,'sum(t2.q_float_null)' ,'sum(t2.q_double_null)' ,
                    'STDDEV(t2.q_int_null)' ,'STDDEV(t2.q_bigint_null)' , 'STDDEV(t2.q_smallint_null)' ,'STDDEV(t2.q_tinyint_null)' ,'STDDEV(t2.q_float_null)' ,'STDDEV(t2.q_double_null)',
                    'APERCENTILE(t2.q_int_null,10)' ,'APERCENTILE(t2.q_bigint_null,20)' , 'APERCENTILE(t2.q_smallint_null,30)' ,'APERCENTILE(t2.q_tinyint_null,40)' ,'APERCENTILE(t2.q_float_null,50)' ,'APERCENTILE(t2.q_double,60)']

        self.calc_aggregate_regular_j = ['twa(t1.q_int)' ,'twa(t1.q_bigint)' , 'twa(t1.q_smallint)' ,'twa(t1.q_tinyint)' ,'twa (t1.q_float)' ,'twa(t1.q_double)' ,
                    'IRATE(t1.q_int)' ,'IRATE(t1.q_bigint)' , 'IRATE(t1.q_smallint)' ,'IRATE(t1.q_tinyint)' ,'IRATE (t1.q_float)' ,'IRATE(t1.q_double)' ,
                    'LEASTSQUARES(t1.q_int,15,3)' , 'LEASTSQUARES(t1.q_bigint,10,1)' , 'LEASTSQUARES(t1.q_smallint,20,3)' ,'LEASTSQUARES(t1.q_tinyint,10,4)' ,'LEASTSQUARES(t1.q_float,6,4)' ,'LEASTSQUARES(t1.q_double,3,1)' ,
                    'twa(t2.q_int)' ,'twa(t2.q_bigint)' , 'twa(t2.q_smallint)' ,'twa(t2.q_tinyint)' ,'twa (t2.q_float)' ,'twa(t2.q_double)' ,
                    'IRATE(t2.q_int)' ,'IRATE(t2.q_bigint)' , 'IRATE(t2.q_smallint)' ,'IRATE(t2.q_tinyint)' ,'IRATE (t2.q_float)' ,'IRATE(t2.q_double)',
                    'LEASTSQUARES(t2.q_int,15,3)' , 'LEASTSQUARES(t2.q_bigint,10,1)' , 'LEASTSQUARES(t2.q_smallint,20,3)' ,'LEASTSQUARES(t2.q_tinyint,10,4)' ,'LEASTSQUARES(t2.q_float,6,4)' ,'LEASTSQUARES(t2.q_double,3,1)' ,
                    'twa(t1.q_int_null)' ,'twa(t1.q_bigint_null)' , 'twa(t1.q_smallint_null)' ,'twa(t1.q_tinyint_null)' ,'twa (t1.q_float_null)' ,'twa(t1.q_double_null)' ,
                    'IRATE(t1.q_int_null)' ,'IRATE(t1.q_bigint_null)' , 'IRATE(t1.q_smallint_null)' ,'IRATE(t1.q_tinyint_null)' ,'IRATE (t1.q_float_null)' ,'IRATE(t1.q_double_null)' ,
                    'LEASTSQUARES(t1.q_int_null,15,3)' , 'LEASTSQUARES(t1.q_bigint_null,10,1)' , 'LEASTSQUARES(t1.q_smallint_null,20,3)' ,'LEASTSQUARES(t1.q_tinyint_null,10,4)' ,'LEASTSQUARES(t1.q_float_null,6,4)' ,'LEASTSQUARES(t1.q_double_null,3,1)' ,
                    'twa(t2.q_int_null)' ,'twa(t2.q_bigint_null)' , 'twa(t2.q_smallint_null)' ,'twa(t2.q_tinyint_null)' ,'twa (t2.q_float_null)' ,'twa(t2.q_double_null)' ,
                    'IRATE(t2.q_int_null)' ,'IRATE(t2.q_bigint_null)' , 'IRATE(t2.q_smallint_null)' ,'IRATE(t2.q_tinyint_null)' ,'IRATE (t2.q_float_null)' ,'IRATE(t2.q_double_null)',
                    'LEASTSQUARES(t2.q_int_null,15,3)' , 'LEASTSQUARES(t2.q_bigint_null,10,1)' , 'LEASTSQUARES(t2.q_smallint_null,20,3)' ,'LEASTSQUARES(t2.q_tinyint_null,10,4)' ,'LEASTSQUARES(t2.q_float_null,6,4)' ,'LEASTSQUARES(t2.q_double_null,3,1)' ]

        self.calc_aggregate_groupbytbname_j = ['twa(t1.q_int)' ,'twa(t1.q_bigint)' , 'twa(t1.q_smallint)' ,'twa(t1.q_tinyint)' ,'twa (t1.q_float)' ,'twa(t1.q_double)' ,
                    'IRATE(t1.q_int)' ,'IRATE(t1.q_bigint)' , 'IRATE(t1.q_smallint)' ,'IRATE(t1.q_tinyint)' ,'IRATE (t1.q_float)' ,'IRATE(t1.q_double)' ,
                    'twa(t2.q_int)' ,'twa(t2.q_bigint)' , 'twa(t2.q_smallint)' ,'twa(t2.q_tinyint)' ,'twa (t2.q_float)' ,'twa(t2.q_double)' ,
                    'IRATE(t2.q_int)' ,'IRATE(t2.q_bigint)' , 'IRATE(t2.q_smallint)' ,'IRATE(t2.q_tinyint)' ,'IRATE (t2.q_float)' ,'IRATE(t2.q_double)' ,
                    'twa(t1.q_int_null)' ,'twa(t1.q_bigint_null)' , 'twa(t1.q_smallint_null)' ,'twa(t1.q_tinyint_null)' ,'twa (t1.q_float_null)' ,'twa(t1.q_double_null)' ,
                    'IRATE(t1.q_int_null)' ,'IRATE(t1.q_bigint_null)' , 'IRATE(t1.q_smallint_null)' ,'IRATE(t1.q_tinyint_null)' ,'IRATE (t1.q_float_null)' ,'IRATE(t1.q_double_null)' ,
                    'twa(t2.q_int_null)' ,'twa(t2.q_bigint_null)' , 'twa(t2.q_smallint_null)' ,'twa(t2.q_tinyint_null)' ,'twa (t2.q_float_null)' ,'twa(t2.q_double_null)' ,
                    'IRATE(t2.q_int_null)' ,'IRATE(t2.q_bigint_null)' , 'IRATE(t2.q_smallint_null)' ,'IRATE(t2.q_tinyint_null)' ,'IRATE (t2.q_float_null)' ,'IRATE(t2.q_double_null)'  ]

        # calc_calculate_all   calc_calculate_regular   calc_calculate_groupbytbname
        # calculation function include [all:spread\+-*/ ||regualr:diff\derivative ||group by tbname:diff\derivative\]
        self.calc_calculate_all = ['SPREAD(ts)'  , 'SPREAD(q_ts)'  , 'SPREAD(q_int)' ,'SPREAD(q_bigint)' , 'SPREAD(q_smallint)' ,'SPREAD(q_tinyint)' ,'SPREAD(q_float)' ,'SPREAD(q_double)' ,
                     '(SPREAD(q_int) + SPREAD(q_bigint))' , '(SPREAD(q_smallint) - SPREAD(q_float))', '(SPREAD(q_double) * SPREAD(q_tinyint))' , '(SPREAD(q_double) / SPREAD(q_float))',
                     'SPREAD(q_ts_null)'  , 'SPREAD(q_int_null)' ,'SPREAD(q_bigint_null)' , 'SPREAD(q_smallint_null)' ,'SPREAD(q_tinyint_null)' ,'SPREAD(q_float_null)' ,'SPREAD(q_double_null)' ,
                     '(SPREAD(q_int_null) + SPREAD(q_bigint_null))' , '(SPREAD(q_smallint_null) - SPREAD(q_float_null))', '(SPREAD(q_double_null) * SPREAD(q_tinyint_null))' , '(SPREAD(q_double_null) / SPREAD(q_float_null))']
        self.calc_calculate_regular = ['DIFF(q_int)' ,'DIFF(q_bigint)' , 'DIFF(q_smallint)' ,'DIFF(q_tinyint)' ,'DIFF(q_float)' ,'DIFF(q_double)' ,
                                'DIFF(q_int,0)' ,'DIFF(q_bigint,0)' , 'DIFF(q_smallint,0)' ,'DIFF(q_tinyint,0)' ,'DIFF(q_float,0)' ,'DIFF(q_double,0)' ,
                                'DIFF(q_int,1)' ,'DIFF(q_bigint,1)' , 'DIFF(q_smallint,1)' ,'DIFF(q_tinyint,1)' ,'DIFF(q_float,1)' ,'DIFF(q_double,1)' ,
                                'DERIVATIVE(q_int,15s,0)' , 'DERIVATIVE(q_bigint,10s,1)' , 'DERIVATIVE(q_smallint,20s,0)' ,'DERIVATIVE(q_tinyint,10s,1)' ,'DERIVATIVE(q_float,6s,0)' ,'DERIVATIVE(q_double,3s,1)',
                                'DIFF(q_int_null)' ,'DIFF(q_bigint_null)' , 'DIFF(q_smallint_null)' ,'DIFF(q_tinyint_null)' ,'DIFF(q_float_null)' ,'DIFF(q_double_null)' ,
                                'DIFF(q_int_null,0)' ,'DIFF(q_bigint_null,0)' , 'DIFF(q_smallint_null,0)' ,'DIFF(q_tinyint_null,0)' ,'DIFF(q_float_null,0)' ,'DIFF(q_double_null,0)' ,
                                'DIFF(q_int_null,1)' ,'DIFF(q_bigint_null,1)' , 'DIFF(q_smallint_null,1)' ,'DIFF(q_tinyint_null,1)' ,'DIFF(q_float_null,1)' ,'DIFF(q_double_null,1)' ,
                                'DERIVATIVE(q_int_null,15s,0)' , 'DERIVATIVE(q_bigint_null,10s,1)' , 'DERIVATIVE(q_smallint_null,20s,0)' ,'DERIVATIVE(q_tinyint_null,10s,1)' ,'DERIVATIVE(q_float_null,6s,0)' ,'DERIVATIVE(q_double_null,3s,1)']
        self.calc_calculate_groupbytbname = self.calc_calculate_regular

        #two table join
        self.calc_calculate_all_j = ['SPREAD(t1.ts)'  , 'SPREAD(t1.q_ts)'  , 'SPREAD(t1.q_int)' ,'SPREAD(t1.q_bigint)' , 'SPREAD(t1.q_smallint)' ,'SPREAD(t1.q_tinyint)' ,'SPREAD(t1.q_float)' ,'SPREAD(t1.q_double)' ,
                    'SPREAD(t2.ts)'  , 'SPREAD(t2.q_ts)'  , 'SPREAD(t2.q_int)' ,'SPREAD(t2.q_bigint)' , 'SPREAD(t2.q_smallint)' ,'SPREAD(t2.q_tinyint)' ,'SPREAD(t2.q_float)' ,'SPREAD(t2.q_double)' ,
                    '(SPREAD(t1.q_int) + SPREAD(t1.q_bigint))' , '(SPREAD(t1.q_tinyint) - SPREAD(t1.q_float))', '(SPREAD(t1.q_double) * SPREAD(t1.q_tinyint))' , '(SPREAD(t1.q_double) / SPREAD(t1.q_tinyint))',
                    '(SPREAD(t2.q_int) + SPREAD(t2.q_bigint))' , '(SPREAD(t2.q_smallint) - SPREAD(t2.q_float))', '(SPREAD(t2.q_double) * SPREAD(t2.q_tinyint))' , '(SPREAD(t2.q_double) / SPREAD(t2.q_tinyint))',
                    '(SPREAD(t1.q_int) + SPREAD(t1.q_smallint))' , '(SPREAD(t2.q_smallint) - SPREAD(t2.q_float))', '(SPREAD(t1.q_double) * SPREAD(t1.q_tinyint))' , '(SPREAD(t1.q_double) / SPREAD(t1.q_float))',
                    'SPREAD(t1.q_ts_null)'  , 'SPREAD(t1.q_int_null)' ,'SPREAD(t1.q_bigint_null)' , 'SPREAD(t1.q_smallint_null)' ,'SPREAD(t1.q_tinyint_null)' ,'SPREAD(t1.q_float_null)' ,'SPREAD(t1.q_double_null)' ,
                    'SPREAD(t2.q_ts_null)'  , 'SPREAD(t2.q_int_null)' ,'SPREAD(t2.q_bigint_null)' , 'SPREAD(t2.q_smallint_null)' ,'SPREAD(t2.q_tinyint_null)' ,'SPREAD(t2.q_float_null)' ,'SPREAD(t2.q_double_null)' ,
                    '(SPREAD(t1.q_int_null) + SPREAD(t1.q_bigint_null))' , '(SPREAD(t1.q_tinyint_null) - SPREAD(t1.q_float_null))', '(SPREAD(t1.q_double_null) * SPREAD(t1.q_tinyint_null))' , '(SPREAD(t1.q_double_null) / SPREAD(t1.q_tinyint_null))',
                    '(SPREAD(t2.q_int_null) + SPREAD(t2.q_bigint_null))' , '(SPREAD(t2.q_smallint_null) - SPREAD(t2.q_float_null))', '(SPREAD(t2.q_double_null) * SPREAD(t2.q_tinyint_null))' , '(SPREAD(t2.q_double_null) / SPREAD(t2.q_tinyint_null))',
                    '(SPREAD(t1.q_int_null) + SPREAD(t1.q_smallint_null))' , '(SPREAD(t2.q_smallint_null) - SPREAD(t2.q_float_null))', '(SPREAD(t1.q_double_null) * SPREAD(t1.q_tinyint_null))' , '(SPREAD(t1.q_double_null) / SPREAD(t1.q_float_null))']
        self.calc_calculate_regular_j = ['DIFF(t1.q_int)' ,'DIFF(t1.q_bigint)' , 'DIFF(t1.q_smallint)' ,'DIFF(t1.q_tinyint)' ,'DIFF(t1.q_float)' ,'DIFF(t1.q_double)' ,
                    'DIFF(t1.q_int,0)' ,'DIFF(t1.q_bigint,0)' , 'DIFF(t1.q_smallint,0)' ,'DIFF(t1.q_tinyint,0)' ,'DIFF(t1.q_float,0)' ,'DIFF(t1.q_double,0)' ,
                    'DIFF(t1.q_int,1)' ,'DIFF(t1.q_bigint,1)' , 'DIFF(t1.q_smallint,1)' ,'DIFF(t1.q_tinyint,1)' ,'DIFF(t1.q_float,1)' ,'DIFF(t1.q_double,1)' ,
                    'DERIVATIVE(t1.q_int,15s,0)' , 'DERIVATIVE(t1.q_bigint,10s,1)' , 'DERIVATIVE(t1.q_smallint,20s,0)' ,'DERIVATIVE(t1.q_tinyint,10s,1)' ,'DERIVATIVE(t1.q_float,6s,0)' ,'DERIVATIVE(t1.q_double,3s,1)' ,
                    'DIFF(t2.q_int)' ,'DIFF(t2.q_bigint)' , 'DIFF(t2.q_smallint)' ,'DIFF(t2.q_tinyint)' ,'DIFF(t2.q_float)' ,'DIFF(t2.q_double)' ,
                    'DIFF(t2.q_int,0)' ,'DIFF(t2.q_bigint,0)' , 'DIFF(t2.q_smallint,0)' ,'DIFF(t2.q_tinyint,0)' ,'DIFF(t2.q_float,0)' ,'DIFF(t2.q_double,0)' ,
                    'DIFF(t2.q_int,1)' ,'DIFF(t2.q_bigint,1)' , 'DIFF(t2.q_smallint,1)' ,'DIFF(t2.q_tinyint,1)' ,'DIFF(t2.q_float,1)' ,'DIFF(t2.q_double,1)' ,
                    'DERIVATIVE(t2.q_int,15s,0)' , 'DERIVATIVE(t2.q_bigint,10s,1)' , 'DERIVATIVE(t2.q_smallint,20s,0)' ,'DERIVATIVE(t2.q_tinyint,10s,1)' ,'DERIVATIVE(t2.q_float,6s,0)' ,'DERIVATIVE(t2.q_double,3s,1)' ,
                    'DIFF(t1.q_int_null)' ,'DIFF(t1.q_bigint_null)' , 'DIFF(t1.q_smallint_null)' ,'DIFF(t1.q_tinyint_null)' ,'DIFF(t1.q_float_null)' ,'DIFF(t1.q_double_null)' ,
                    'DIFF(t1.q_int_null,0)' ,'DIFF(t1.q_bigint_null,0)' , 'DIFF(t1.q_smallint_null,0)' ,'DIFF(t1.q_tinyint_null,0)' ,'DIFF(t1.q_float_null,0)' ,'DIFF(t1.q_double_null,0)' ,
                    'DIFF(t1.q_int_null,1)' ,'DIFF(t1.q_bigint_null,1)' , 'DIFF(t1.q_smallint_null,1)' ,'DIFF(t1.q_tinyint_null,1)' ,'DIFF(t1.q_float_null,1)' ,'DIFF(t1.q_double_null,1)' ,
                    'DERIVATIVE(t1.q_int_null,15s,0)' , 'DERIVATIVE(t1.q_bigint_null,10s,1)' , 'DERIVATIVE(t1.q_smallint_null,20s,0)' ,'DERIVATIVE(t1.q_tinyint_null,10s,1)' ,'DERIVATIVE(t1.q_float_null,6s,0)' ,'DERIVATIVE(t1.q_double_null,3s,1)' ,
                    'DIFF(t2.q_int_null)' ,'DIFF(t2.q_bigint_null)' , 'DIFF(t2.q_smallint_null)' ,'DIFF(t2.q_tinyint_null)' ,'DIFF(t2.q_float_null)' ,'DIFF(t2.q_double_null)' ,
                    'DIFF(t2.q_int_null,0)' ,'DIFF(t2.q_bigint_null,0)' , 'DIFF(t2.q_smallint_null,0)' ,'DIFF(t2.q_tinyint_null,0)' ,'DIFF(t2.q_float_null,0)' ,'DIFF(t2.q_double_null,0)' ,
                    'DIFF(t2.q_int_null,1)' ,'DIFF(t2.q_bigint_null,1)' , 'DIFF(t2.q_smallint_null,1)' ,'DIFF(t2.q_tinyint_null,1)' ,'DIFF(t2.q_float_null,1)' ,'DIFF(t2.q_double_null,1)' ,
                    'DERIVATIVE(t2.q_int_null,15s,0)' , 'DERIVATIVE(t2.q_bigint_null,10s,1)' , 'DERIVATIVE(t2.q_smallint_null,20s,0)' ,'DERIVATIVE(t2.q_tinyint_null,10s,1)' ,'DERIVATIVE(t2.q_float_null,6s,0)' ,'DERIVATIVE(t2.q_double_null,3s,1)']
        self.calc_calculate_groupbytbname_j = self.calc_calculate_regular_j

        #inter  && calc_aggregate_all\calc_aggregate_regular\calc_select_all
        self.interval_sliding = ['interval(4w) sliding(1w) ','interval(1w) sliding(1d) ','interval(1d) sliding(1h) ' ,
                    'interval(1h) sliding(1m) ','interval(1m) sliding(1s) ','interval(1s) sliding(10a) ',
                    'interval(1y) ','interval(1n) ','interval(1w) ','interval(1d) ','interval(1h) ','interval(1m) ','interval(1s) ' ,'interval(10a)',
                    'interval(1y,1n) ','interval(1n,1w) ','interval(1w,1d) ','interval(1d,1h) ','interval(1h,1m) ','interval(1m,1s) ','interval(1s,10a) ' ,'interval(100a,30a)']

        self.conn1 = taos.connect(host="127.0.0.1", user="root", password="taosdata", config=tdDnodes.sim.cfgPath)
        self.cur1 = self.conn1.cursor()
        print(self.cur1)
        self.cur1.execute("use %s ;" %self.db_nest)
        sql = 'select * from stable_1 limit 5;'
        self.cur1.execute(sql)

    def dropandcreateDB_random(self,database,n):
        ts = 1630000000000
        num_random = 100
        fake = Faker('zh_CN')
        tdSql.execute('''drop database if exists %s ;''' %database)
        tdSql.execute('''create database %s keep 36500;'''%database)
        tdSql.execute('''use %s;'''%database)

        tdSql.execute('''create stable stable_1 (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) \
                tags(loc nchar(100) , t_int int , t_bigint bigint , t_smallint smallint , t_tinyint tinyint, t_bool bool , t_binary binary(100) , t_nchar nchar(100) ,t_float float , t_double double , t_ts timestamp);''')
        tdSql.execute('''create stable stable_2 (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) \
                tags(loc nchar(100) , t_int int , t_bigint bigint , t_smallint smallint , t_tinyint tinyint, t_bool bool , t_binary binary(100) , t_nchar nchar(100) ,t_float float , t_double double , t_ts timestamp);''')

        tdSql.execute('''create stable stable_null_data (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) \
                tags(loc nchar(100) , t_int int , t_bigint bigint , t_smallint smallint , t_tinyint tinyint, t_bool bool , t_binary binary(100) , t_nchar nchar(100) ,t_float float , t_double double , t_ts timestamp);''')

        tdSql.execute('''create stable stable_null_childtable (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) \
                tags(loc nchar(100) , t_int int , t_bigint bigint , t_smallint smallint , t_tinyint tinyint, t_bool bool , t_binary binary(100) , t_nchar nchar(100) ,t_float float , t_double double , t_ts timestamp);''')

        #tdSql.execute('''create table stable_1_1 using stable_1 tags('stable_1_1', '0' , '0' , '0' , '0' , 0 , 'binary1' , 'nchar1' , '0' , '0' ,'0') ;''')
        tdSql.execute('''create table stable_1_1 using stable_1 tags('stable_1_1', '%d' , '%d', '%d' , '%d' , 0 , 'binary1.%s' , 'nchar1.%s' , '%f', '%f' ,'%d') ;'''
                      %(fake.random_int(min=-2147483647, max=2147483647, step=1), fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1),
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pystr() ,fake.pystr() ,fake.pyfloat(),fake.pyfloat(),fake.random_int(min=-2147483647, max=2147483647, step=1)))
        tdSql.execute('''create table stable_1_2 using stable_1 tags('stable_1_2', '2147483647' , '9223372036854775807' , '32767' , '127' , 1 , 'binary2' , 'nchar2' , '2' , '22' , \'1999-09-09 09:09:09.090\') ;''')
        tdSql.execute('''create table stable_1_3 using stable_1 tags('stable_1_3', '-2147483647' , '-9223372036854775807' , '-32767' , '-127' , false , 'binary3' , 'nchar3nchar3' , '-3.3' , '-33.33' , \'2099-09-09 09:09:09.090\') ;''')
        #tdSql.execute('''create table stable_1_4 using stable_1 tags('stable_1_4', '0' , '0' , '0' , '0' , 0 , '0' , '0' , '0' , '0' ,'0') ;''')
        tdSql.execute('''create table stable_1_4 using stable_1 tags('stable_1_4', '%d' , '%d', '%d' , '%d' , 1 , 'binary1.%s' , 'nchar1.%s' , '%f', '%f' ,'%d') ;'''
                      %(fake.random_int(min=-2147483647, max=2147483647, step=1), fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1),
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pystr() ,fake.pystr() ,fake.pyfloat(),fake.pyfloat(),fake.random_int(min=-2147483647, max=2147483647, step=1)))

        # tdSql.execute('''create table stable_2_1 using stable_2 tags('stable_2_1' , '0' , '0' , '0' , '0' , 0 , 'binary21' , 'nchar21' , '0' , '0' ,'0') ;''')
        # tdSql.execute('''create table stable_2_2 using stable_2 tags('stable_2_2' , '0' , '0' , '0' , '0' , 0 , '0' , '0' , '0' , '0' ,'0') ;''')

        # tdSql.execute('''create table stable_null_data_1 using stable_null_data tags('stable_null_data_1', '0' , '0' , '0' , '0' , 0 , '0' , '0' , '0' , '0' ,'0') ;''')

        tdSql.execute('''create table stable_2_1 using stable_2 tags('stable_2_1' , '0' , '0' , '0' , '0' , 0 , 'binary21' , 'nchar21' , '0' , '0' ,\'2099-09-09 09:09:09.090\') ;''')
        tdSql.execute('''create table stable_2_2 using stable_2 tags('stable_2_2' , '%d' , '%d', '%d' , '%d' , 0 , 'binary2.%s' , 'nchar2.%s' , '%f', '%f' ,'%d') ;'''
                      %(fake.random_int(min=-2147483647, max=2147483647, step=1), fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1),
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pystr() ,fake.pystr() ,fake.pyfloat(),fake.pyfloat(),fake.random_int(min=-2147483647, max=2147483647, step=1)))

        tdSql.execute('''create table stable_null_data_1 using stable_null_data tags('stable_null_data_1', '%d' , '%d', '%d' , '%d' , 1 , 'binary1.%s' , 'nchar1.%s' , '%f', '%f' ,'%d') ;'''
                      %(fake.random_int(min=-2147483647, max=2147483647, step=1), fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1),
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pystr() ,fake.pystr() ,fake.pyfloat(),fake.pyfloat(),fake.random_int(min=-2147483647, max=2147483647, step=1)))

        #regular table
        tdSql.execute('''create table regular_table_1 \
                    (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                    q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                    q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                    q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) ;''')
        tdSql.execute('''create table regular_table_2 \
                    (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                    q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                    q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                    q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) ;''')
        tdSql.execute('''create table regular_table_3 \
                    (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                    q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                    q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                    q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) ;''')

        tdSql.execute('''create table regular_table_null \
                    (ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint , q_float float , q_double double , q_bool bool , q_binary binary(100) , q_nchar nchar(100) , q_ts timestamp , \
                    q_binary1 binary(100) , q_nchar1 nchar(100) ,q_binary2 binary(100) , q_nchar2 nchar(100) ,q_binary3 binary(100) , q_nchar3 nchar(100) ,q_binary4 binary(100) , q_nchar4 nchar(100) ,\
                    q_binary5 binary(100) , q_nchar5 nchar(100) ,q_binary6 binary(100) , q_nchar6 nchar(100) ,q_binary7 binary(100) , q_nchar7 nchar(100) ,q_binary8 binary(100) , q_nchar8 nchar(100) ,\
                    q_int_null int , q_bigint_null bigint , q_smallint_null smallint , q_tinyint_null tinyint, q_float_null float , q_double_null double , q_bool_null bool , q_binary_null binary(20) , q_nchar_null nchar(20) , q_ts_null timestamp) ;''')

        for i in range(1, num_random*n + 1):
            tdSql.execute('''insert into stable_1_1  (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double , q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 0, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000, fake.random_int(min=-2147483647, max=2147483647, step=1),
                        fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1),
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))
            tdSql.execute('''insert into  regular_table_1 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 0, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000, fake.random_int(min=-2147483647, max=2147483647, step=1) ,
                        fake.random_int(min=-9223372036854775807, max=9223372036854775807, step=1) ,
                        fake.random_int(min=-32767, max=32767, step=1) , fake.random_int(min=-127, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

            tdSql.execute('''insert into stable_1_2 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8)\
                        values(%d, %d, %d, %d, %d, %f, %f, 1, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000*60*60*2, fake.random_int(min=0, max=2147483647, step=1),
                        fake.random_int(min=0, max=9223372036854775807, step=1),
                        fake.random_int(min=0, max=32767, step=1) , fake.random_int(min=0, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))
            tdSql.execute('''insert into regular_table_2 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 1, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000, fake.random_int(min=0, max=2147483647, step=1),
                        fake.random_int(min=0, max=9223372036854775807, step=1),
                        fake.random_int(min=0, max=32767, step=1) , fake.random_int(min=0, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

            tdSql.execute('''insert into stable_1_2 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 1, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000*60*60*2 +1, fake.random_int(min=-2147483647, max=0, step=1),
                        fake.random_int(min=-9223372036854775807, max=0, step=1),
                        fake.random_int(min=-32767, max=0, step=1) , fake.random_int(min=-127, max=0, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i +1, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))
            tdSql.execute('''insert into regular_table_2 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 1, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000 +1, fake.random_int(min=-2147483647, max=0, step=1),
                        fake.random_int(min=-9223372036854775807, max=0, step=1),
                        fake.random_int(min=-32767, max=0, step=1) , fake.random_int(min=-127, max=0, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i +1, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

            tdSql.execute('''insert into stable_2_1 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 0, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000*60*60*4, fake.random_int(min=-0, max=2147483647, step=1),
                        fake.random_int(min=-0, max=9223372036854775807, step=1),
                        fake.random_int(min=-0, max=32767, step=1) , fake.random_int(min=-0, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

            tdSql.execute('''insert into stable_2_1 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 0, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000*60*60*4 +1, fake.random_int(min=-0, max=2147483647, step=1),
                        fake.random_int(min=-0, max=9223372036854775807, step=1),
                        fake.random_int(min=-0, max=32767, step=1) , fake.random_int(min=-0, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

            tdSql.execute('''insert into stable_2_1 (ts , q_int , q_bigint , q_smallint , q_tinyint , q_float , q_double, q_bool , q_binary , q_nchar, q_ts,\
                        q_binary1 , q_nchar1 , q_binary2 , q_nchar2 , q_binary3 , q_nchar3 , q_binary4 , q_nchar4 , q_binary5 , q_nchar5 , q_binary6 , q_nchar6 , q_binary7 , q_nchar7, q_binary8 , q_nchar8) \
                        values(%d, %d, %d, %d, %d, %f, %f, 0, 'binary.%s', 'nchar.%s', %d, 'binary1.%s', 'nchar1.%s', 'binary2.%s', 'nchar2.%s', 'binary3.%s', 'nchar3.%s', \
                        'binary4.%s', 'nchar4.%s', 'binary5.%s', 'nchar5.%s', 'binary6.%s', 'nchar6.%s', 'binary7.%s', 'nchar7.%s', 'binary8.%s', 'nchar8.%s') ;'''
                        % (ts + i*1000*60*60*4 +10, fake.random_int(min=-0, max=2147483647, step=1),
                        fake.random_int(min=-0, max=9223372036854775807, step=1),
                        fake.random_int(min=-0, max=32767, step=1) , fake.random_int(min=-0, max=127, step=1) ,
                        fake.pyfloat() , fake.pyfloat() , fake.pystr() , fake.pystr() , ts + i, fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() ,
                        fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr() , fake.pystr()))

        tdSql.query("select count(*) from stable_1;")
        tdSql.checkData(0,0,3*num_random*n)
        tdSql.query("select count(*) from regular_table_1;")
        tdSql.checkData(0,0,num_random*n)

    def restartDnodes(self):
        pass
        # tdDnodes.stop(1)
        # tdDnodes.start(1)

    def explain_sql(self,sql):
        # #执行sql解析
        sql = "explain " + sql
        tdLog.info(sql)
        tdSql.query(sql)
        #pass

    
    def time_nest(self,timelist):

        tdLog.info("==========%s===start=============" %timelist)
        os.system("rm -rf %s/%s.sql" % (self.testcasePath,self.testcaseFilename))

        self.dropandcreateDB_random("%s" %self.db_nest, 1)

        if (timelist == ['NOW','TODAY']) or (timelist == ['TIMEZONE']):
            time_functions = timelist
            fun_fix_column = ['()']
            fun_column_1 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","")
            fun_column_2 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","")

            fun_fix_column_j = ['()']
            fun_column_join_1 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","")
            fun_column_join_2 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","")

        elif (timelist == ['TIMETRUNCATE']):
            time_functions = timelist

            t = time.time()
            t_to_s =  time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t))
            fun_fix_column = ['q_ts','ts','_c0','_C0','_rowts','1600000000000','1600000000000000','1600000000000000000',
                        '%d' %t, '%d000' %t, '%d000000' %t,'t_to_s']

            timeunits = ['1a' ,'1s', '1m' ,'1h', '1d']
            timeunit = str(random.sample(timeunits,1)).replace("[","").replace("]","").replace("'","")

            column_1 = ['(%s,timeutil)'%(random.sample(fun_fix_column,1))]
            fun_column_1 = random.sample(time_functions,1)+random.sample(column_1,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_1 = str(time_fun_1).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s)

            column_2 = ['(%s,timeutil)'%(random.sample(fun_fix_column,1))]
            fun_column_2 = random.sample(time_functions,1)+random.sample(column_2,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_2 = str(time_fun_2).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s)

            fun_fix_column_j = ['(t1.q_ts)','(t1.ts)', '(t2.q_ts)','(t2.ts)','(1600000000000)','(1600000000000000)','(1600000000000000000)',
                        '(%d)' %t, '(%d000)' %t, '(%d000000)' %t,'t_to_s']

            column_j1 = ['(%s,timeutil)'%(random.sample(fun_fix_column_j,1))]
            fun_column_join_1 = random.sample(time_functions,1)+random.sample(column_j1,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_1 = str(time_fun_join_1).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s)

            column_j2 = ['(%s,timeutil)'%(random.sample(fun_fix_column_j,1))]
            fun_column_join_2 = random.sample(time_functions,1)+random.sample(column_j2,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_2 = str(time_fun_join_2).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s)

        elif (timelist == ['TO_ISO8601']):
            time_functions = timelist

            t = time.time()
            fun_fix_column = ['(now())','(ts)','(q_ts)','(_rowts)','(_c0)','(_C0)',
                              '(1600000000000)','(1600000000000000)','(1600000000000000000)',
                              '(%d)' %t, '(%d000)' %t, '(%d000000)' %t]

            fun_column_1 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","")

            fun_column_2 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","")

            fun_fix_column_j = ['(t1.q_ts)','(t1.ts)', '(t2.q_ts)','(t2.ts)','(1600000000000)','(1600000000000000)','(1600000000000000000)','(now())',
                                '(%d)' %t, '(%d000)' %t, '(%d000000)' %t]

            fun_column_join_1 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","")

            fun_column_join_2 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","")

        elif (timelist == ['TO_UNIXTIMESTAMP']):
            time_functions = timelist

            t = time.time()
            t_to_s =  time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t))
            fun_fix_column = ['(q_nchar)','(q_nchar1)','(q_nchar2)','(q_nchar3)','(q_nchar4)','(q_nchar_null)','(q_binary)','(q_binary5)','(q_binary6)','(q_binary7)','(q_binary8)','(q_binary_null)','(t_to_s)']

            fun_column_1 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("t_to_s","'t_to_s'")
            time_fun_1 = str(time_fun_1).replace("t_to_s","%s" %t_to_s)

            fun_column_2 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("t_to_s","'t_to_s'")
            time_fun_2 = str(time_fun_2).replace("t_to_s","%s" %t_to_s)

            fun_fix_column_j = ['(t1.q_nchar)','(t1.q_binary)', '(t2.q_nchar)','(t2.q_binary)','(t_to_s)']

            fun_column_join_1 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("t_to_s","'t_to_s'")
            time_fun_join_1 = str(time_fun_join_1).replace("t_to_s","%s" %t_to_s)

            fun_column_join_2 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("t_to_s","'t_to_s'")
            time_fun_join_2 = str(time_fun_join_2).replace("t_to_s","%s" %t_to_s)

        elif (timelist == ['TIMEDIFF_1']):
            time_functions = timelist

            t = time.time()
            t_to_s =  time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t))
            timeunits = [ '1a' ,'1s', '1m' ,'1h', '1d']
            timeunit = str(random.sample(timeunits,1)).replace("[","").replace("]","").replace("'","")

            fun_fix_column = ['q_ts','ts','_c0','_C0','_rowts','1600000000000','1600000000000000','1600000000000000000',
                              '%d' %t, '%d000' %t, '%d000000' %t,'t_to_s']

            column_1,column_2 = random.sample(fun_fix_column,1),random.sample(fun_fix_column,1)
            column_12 = ['(%s,%s,timeutil)'%(column_1,column_2)]
            fun_column_1 = random.sample(time_functions,1)+random.sample(column_12,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_1 = str(time_fun_1).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s).replace("_1","")

            column_3,column_4 = random.sample(fun_fix_column,1),random.sample(fun_fix_column,1)
            column_34 = ['(%s,%s,timeutil)'%(column_3,column_4)]
            fun_column_2 = random.sample(time_functions,1)+random.sample(column_34,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_2 = str(time_fun_2).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s).replace("_1","")

            fun_fix_column_j = ['(t1.q_ts)','(t1.ts)', '(t2.q_ts)','(t2.ts)','1600000000000','1600000000000000','1600000000000000000',
                              '%d' %t, '%d000' %t, '%d000000' %t,'t_to_s']

            column_j1,column_j2 = random.sample(fun_fix_column_j,1),random.sample(fun_fix_column_j,1)
            column_j12 = ['(%s,%s,timeutil)'%(column_j1,column_j2)]
            fun_column_join_1 = random.sample(time_functions,1)+random.sample(column_j12,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_1 = str(time_fun_join_1).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s).replace("_1","")

            column_j3,column_j4 = random.sample(fun_fix_column_j,1),random.sample(fun_fix_column_j,1)
            column_j34 = ['(%s,%s,timeutil)'%(column_j3,column_j4)]
            fun_column_join_2 = random.sample(time_functions,1)+random.sample(column_j34,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_2 = str(time_fun_join_2).replace("timeutil","%s" %timeunit).replace("t_to_s","%s" %t_to_s).replace("_1","")

        elif (timelist == ['TIMEDIFF_2']):
            time_functions = timelist

            t = time.time()
            t_to_s =  time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t))

            fun_fix_column = ['q_ts','ts','_c0','_C0','_rowts','1600000000000','1600000000000000','1600000000000000000',
                              '%d' %t, '%d000' %t, '%d000000' %t,'t_to_s']

            column_1,column_2 = random.sample(fun_fix_column,1),random.sample(fun_fix_column,1)
            column_12 = ['(%s,%s)'%(column_1,column_2)]
            fun_column_1 = random.sample(time_functions,1)+random.sample(column_12,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_1 = str(time_fun_1).replace("t_to_s","%s" %t_to_s).replace("_2","")

            column_3,column_4 = random.sample(fun_fix_column,1),random.sample(fun_fix_column,1)
            column_34 = ['(%s,%s)'%(column_3,column_4)]
            fun_column_2 = random.sample(time_functions,1)+random.sample(column_34,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_2 = str(time_fun_2).replace("t_to_s","%s" %t_to_s).replace("_2","")

            fun_fix_column_j = ['(t1.q_ts)','(t1.ts)', '(t2.q_ts)','(t2.ts)','1600000000000','1600000000000000','1600000000000000000',
                              '%d' %t, '%d000' %t, '%d000000' %t,'t_to_s']

            column_j1,column_j2 = random.sample(fun_fix_column_j,1),random.sample(fun_fix_column_j,1)
            column_j12 = ['(%s,%s)'%(column_j1,column_j2)]
            fun_column_join_1 = random.sample(time_functions,1)+random.sample(column_j12,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_1 = str(time_fun_join_1).replace("t_to_s","%s" %t_to_s).replace("_2","")

            column_j3,column_j4 = random.sample(fun_fix_column_j,1),random.sample(fun_fix_column_j,1)
            column_j34 = ['(%s,%s)'%(column_j3,column_j4)]
            fun_column_join_2 = random.sample(time_functions,1)+random.sample(column_j34,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("\"","").replace("t_to_s","'t_to_s'")
            time_fun_join_2 = str(time_fun_join_2).replace("t_to_s","%s" %t_to_s).replace("_2","")

        elif (timelist == ['ELAPSED']):
            time_functions = timelist

            fun_fix_column = ['(ts)','(_c0)','(_C0)','(_rowts)','(ts,time_unit)','(_c0,time_unit)','(_C0,time_unit)','(_rowts,time_unit)']

            time_units = ['1s','1m','1h','1d','1a']
            time_unit1 = str(random.sample(time_units,1)).replace("[","").replace("]","").replace("'","")
            time_unit2 = str(random.sample(time_units,1)).replace("[","").replace("]","").replace("'","")

            fun_column_1 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("time_unit","%s" %time_unit1)

            fun_column_2 = random.sample(time_functions,1)+random.sample(fun_fix_column,1)
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("time_unit","%s" %time_unit2)

            fun_fix_column_j = ['(t1.ts)', '(t2.ts)','(t1.ts,time_unit)','(t1.ts,time_unit)','(t2.ts,time_unit)','(t2.ts,time_unit)']

            fun_column_join_1 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace(", ","").replace("time_unit","%s" %time_unit1)

            fun_column_join_2 = random.sample(time_functions,1)+random.sample(fun_fix_column_j,1)
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace(", ","").replace("time_unit","%s" %time_unit2)

        elif (timelist == ['CAST']) :
            str_functions = timelist
            #下面的4个是全的，这个只是1个
            i = random.randint(1,4)
            if i ==1:
                print('===========cast_1===========')
                fun_fix_column = ['q_bool','q_bool_null','q_bigint','q_bigint_null','q_smallint','q_smallint_null',
                'q_tinyint','q_tinyint_null','q_int','q_int_null','q_float','q_float_null','q_double','q_double_null']
                type_names = ['BIGINT','BINARY(100)','TIMESTAMP','NCHAR(100)','BIGINT UNSIGNED']

                type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
                time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","")

                type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
                time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","")

                fun_fix_column_j = ['t1.q_bool','t1.q_bool_null','t1.q_bigint','t1.q_bigint_null','t1.q_smallint','t1.q_smallint_null',
                't1.q_tinyint','t1.q_tinyint_null','t1.q_int','t1.q_int_null','t1.q_float','t1.q_float_null','t1.q_double','t1.q_double_null',
                't2.q_bool','t2.q_bool_null','t2.q_bigint','t2.q_bigint_null','t2.q_smallint','t2.q_smallint_null',
                't2.q_tinyint','t2.q_tinyint_null','t2.q_int','t2.q_int_null','t2.q_float','t2.q_float_null','t2.q_double','t2.q_double_null']

                type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
                time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","")

                type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
                time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","")

            elif i==2:
                print('===========cast_2===========')
                fun_fix_column = ['q_binary','q_binary_null','q_binary1','q_binary2','q_binary3','q_binary4']
                type_names = ['BIGINT','BINARY(100)','NCHAR(100)','BIGINT UNSIGNED']

                type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
                time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","")

                type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
                time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","")

                fun_fix_column_j = ['t1.q_binary','t1.q_binary_null','t1.q_binary1','t1.q_binary2','t1.q_binary3','t1.q_binary4',
                        't2.q_binary','t2.q_binary_null','t2.q_binary1','t2.q_binary2','t2.q_binary3','t2.q_binary4']

                type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
                time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","")

                type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
                time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","")

            elif i==3:
                print('===========cast_3===========')
                fun_fix_column = ['q_nchar','q_nchar_null','q_nchar5','q_nchar6','q_nchar7','q_nchar8']
                type_names = ['BIGINT','NCHAR(100)','BIGINT UNSIGNED']

                type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
                time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","")

                type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
                time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","")

                fun_fix_column_j = ['t1.q_nchar','t1.q_nchar_null','t1.q_nchar5','t1.q_nchar6','t1.q_nchar7','t1.q_nchar8',
                        't2.q_nchar','t2.q_nchar_null','t2.q_nchar5','t2.q_nchar6','t2.q_nchar7','t2.q_nchar8']

                type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
                time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","")

                type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
                time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","")

            elif i==4:
                print('===========cast_4===========')
                fun_fix_column = ['q_ts','q_ts_null','_C0','_c0','ts','_rowts']
                type_names = ['BIGINT','TIMESTAMP','BIGINT UNSIGNED']

                type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
                time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","")

                type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
                time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","")

                fun_fix_column_j = ['t1.q_ts','t1.q_ts_null','t1.ts','t2.q_ts','t2.q_ts_null','t2.ts']

                type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
                time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","")

                type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
                fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
                time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","")

        elif (timelist == ['CAST_1']) :
            str_functions = timelist

            print('===========cast_1===========')
            fun_fix_column = ['q_bool','q_bool_null','q_bigint','q_bigint_null','q_smallint','q_smallint_null',
            'q_tinyint','q_tinyint_null','q_int','q_int_null','q_float','q_float_null','q_double','q_double_null']
            type_names = ['BIGINT','BINARY(100)','TIMESTAMP','NCHAR(100)','BIGINT UNSIGNED']

            type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace("_1","")

            type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace("_1","")

            fun_fix_column_j = ['t1.q_bool','t1.q_bool_null','t1.q_bigint','t1.q_bigint_null','t1.q_smallint','t1.q_smallint_null',
            't1.q_tinyint','t1.q_tinyint_null','t1.q_int','t1.q_int_null','t1.q_float','t1.q_float_null','t1.q_double','t1.q_double_null',
            't2.q_bool','t2.q_bool_null','t2.q_bigint','t2.q_bigint_null','t2.q_smallint','t2.q_smallint_null',
            't2.q_tinyint','t2.q_tinyint_null','t2.q_int','t2.q_int_null','t2.q_float','t2.q_float_null','t2.q_double','t2.q_double_null']

            type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace("_1","")

            type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace("_1","")

        elif (timelist == ['CAST_2']) :
            str_functions = timelist
            print('===========cast_2===========')
            fun_fix_column = ['q_binary','q_binary_null','q_binary1','q_binary2','q_binary3','q_binary4']
            type_names = ['BIGINT','BINARY(100)','NCHAR(100)','BIGINT UNSIGNED']

            type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace("_2","")

            type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace("_2","")

            fun_fix_column_j = ['t1.q_binary','t1.q_binary_null','t1.q_binary1','t1.q_binary2','t1.q_binary3','t1.q_binary4',
                    't2.q_binary','t2.q_binary_null','t2.q_binary1','t2.q_binary2','t2.q_binary3','t2.q_binary4']

            type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace("_2","")

            type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace("_2","")

        elif (timelist == ['CAST_3']) :
            str_functions = timelist
            print('===========cast_3===========')
            fun_fix_column = ['q_nchar','q_nchar_null','q_nchar5','q_nchar6','q_nchar7','q_nchar8']
            type_names = ['BIGINT','NCHAR(100)','BIGINT UNSIGNED']

            type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace("_3","")

            type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace("_3","")

            fun_fix_column_j = ['t1.q_nchar','t1.q_nchar_null','t1.q_nchar5','t1.q_nchar6','t1.q_nchar7','t1.q_nchar8',
                    't2.q_nchar','t2.q_nchar_null','t2.q_nchar5','t2.q_nchar6','t2.q_nchar7','t2.q_nchar8']

            type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace("_3","")

            type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace("_3","")

        elif (timelist == ['CAST_4']) :
            str_functions = timelist
            print('===========cast_4===========')
            fun_fix_column = ['q_ts','q_ts_null','_C0','_c0','ts','_rowts']
            type_names = ['BIGINT','TIMESTAMP','BIGINT UNSIGNED']

            type_name1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name1+')'
            time_fun_1 = str(fun_column_1).replace("[","").replace("]","").replace("'","").replace("_4","")

            type_name2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column,1))+' AS '+type_name2+')'
            time_fun_2 = str(fun_column_2).replace("[","").replace("]","").replace("'","").replace("_4","")

            fun_fix_column_j = ['t1.q_ts','t1.q_ts_null','t1.ts','t2.q_ts','t2.q_ts_null','t2.ts']

            type_name_j1 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_1 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j1+')'
            time_fun_join_1 = str(fun_column_join_1).replace("[","").replace("]","").replace("'","").replace("_4","")

            type_name_j2 = str(random.sample(type_names,1)).replace("[","").replace("]","").replace("'","")
            fun_column_join_2 = str(random.sample(str_functions,1))+'('+str(random.sample(fun_fix_column_j,1))+' AS '+type_name_j2+')'
            time_fun_join_2 = str(fun_column_join_2).replace("[","").replace("]","").replace("'","").replace("_4","")

        tdSql.query("select 1-1 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1 , timediff(asct1,now)  from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts as ts1 from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(100)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) \
                or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts2 , asct1,now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts as ts2 from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(100)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  max(asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += "from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-2 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(asct1,now),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1,  " % time_fun_1
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts ts from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s )" % random.choice(self.order_where)
                sql += "%s " % random.choice(self.unionall_or_union)
                sql += "select  ts ,  timediff(asct2,now),now(),today(),timezone() from  ( select  "
                sql += "%s as asct2,  " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts ts from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                #sql += "%s " % random.choice(having_support)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts , (asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1,  " % time_fun_1
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts ts from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s )" % random.choice(self.order_where)
                sql += "%s " % random.choice(self.unionall_or_union)
                sql += "select  ts , asct2,now(),today(),timezone() from  ( select  "
                sql += "%s as asct2,  " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s, " % random.choice(self.q_select)
                sql += "ts ts from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  min(asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1  " % time_fun_1
                sql += " from regular_table_1 where "
                sql += "%s )" % random.choice(self.q_where)
                sql += "%s " % random.choice(self.unionall_or_union)
                sql += "select   avg(asct2),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct2  " % time_fun_2
                sql += " from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-3 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(asct1,now) from  ( select  "
                sql += "%s as asct1, ts ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s " % random.choice(self.q_select)
                sql += "from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s select " % random.choice(self.unionall_or_union)
                sql += "%s as asct2, ts ," % time_fun_2
                sql += "%s as asct1, " % time_fun_1
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s " % random.choice(self.q_select)
                sql += "from regular_table_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " order by asct1 desc  "
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts , (asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1, ts ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s " % random.choice(self.q_select)
                sql += " from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s select " % random.choice(self.unionall_or_union)
                sql += "%s as asct2, ts ," % time_fun_2
                sql += "%s as asct1, " % time_fun_1
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s " % random.choice(self.q_select)
                sql += "from regular_table_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " order by asct1 desc  "
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  abs(asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1," % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += "from regular_table_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s select " % random.choice(self.unionall_or_union)
                sql += "%s as asct2," % time_fun_2
                sql += "%s as asct1  " % time_fun_1
                sql += "from regular_table_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " order by asct1 asc "
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-4 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), timediff(asct1,now) from  ( select  t1.ts as ts1,"
                sql += "%s as asct11, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "%s as asct12, " % time_fun_join_1
                sql += "t1.%s as asct111, " % random.choice(self.q_select)
                sql += "t2.%s as asct121, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s " % random.choice(self.q_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(100)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), (asct1) from  ( select  t1.ts as ts1,"
                sql += "%s as asct10, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "%s as asct11, " % time_fun_join_1
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s " % random.choice(self.q_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(100)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  floor(asct1) from  ( select "
                sql += "%s as asct10, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "%s as asct11" % time_fun_join_1
                sql += " from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s " % random.choice(self.q_u_or_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-5 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['ELAPSED']) :
                sql = "select  now(),today(),timezone(), "
                sql += "%s, " % time_fun_1
                sql += "%s  " % time_fun_2
                sql += " from  ( select  * from regular_table_1 ) where "
                sql += "%s " % random.choice(self.q_where)
                sql += " ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            else:
                sql = "select  ts ,now(),today(),timezone(), "
                sql += "%s as asct11, " % time_fun_1
                sql += "%s as asct12, " % random.choice(self.q_select)
                sql += "%s as asct13, " % random.choice(self.q_select)
                sql += "%s as asct14  " % time_fun_2
                sql += " from  ( select  * from regular_table_1 ) where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += " ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(100)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-6 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,ts ,timediff(ts1,ts), timediff(asct1,now) from  ( select  t1.ts as ts1,"
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t2.%s as asct12, " % random.choice(self.q_select)
                sql += "%s as asct13, " % time_fun_join_1
                sql += "t2.ts   from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s  )" % random.choice(self.q_u_or_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts ,timediff(ts1,ts), (asct1) from  ( select  t1.ts as ts1,"
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t2.%s as asct12, " % random.choice(self.q_select)
                sql += "%s as asct13, " % time_fun_join_1
                sql += "t2.ts  from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s  )" % random.choice(self.q_u_or_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select   (asct1)*111 from  ( select  "
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "%s as asct122 " % time_fun_join_1
                sql += " from regular_table_1 t1 , regular_table_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.q_u_where)
                sql += "and %s  )" % random.choice(self.q_u_or_where)
                sql += "%s " % random.choice(self.limit1_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-7 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,m1 , timediff(asct1,now) from  ( select  "
                sql += "%s as asct1, ts as m1," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.t_select)
                sql += "ts as ts1 from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  tm1,tm2 , (asct1),now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1, ts as tm1," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.t_select)
                sql += "ts as tm2 from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  (asct1)/asct2 ,now(),today(),timezone()  from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += "from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-8 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  tm1,tm2 , timediff(asct1,now) "
                sql += "from  ( select  "
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s as asct1, ts as tm1," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.t_select)
                sql += "ts as tm2 from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts2 , (asct1),now(),today(),timezone()  "
                sql += "from  ( select  "
                sql += "%s, " % random.choice(self.s_s_select)
                sql += "%s as asct1, ts as ts1," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.t_select)
                sql += "ts as ts2 from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  floor(abs(asct1)),now(),today(),timezone()  "
                sql += "from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += "from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-9 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), timediff(asct1,now) from  ( select  t1.ts as ts1,"
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_join_where)
                sql += "and %s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), asct1 from  ( select  t1.ts as ts1,"
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_join_where)
                sql += "and %s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  min(asct1*110) from  ( select  "
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1 " % time_fun_join_2
                sql += "from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_join_where)
                sql += "and %s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.error(sql)

        self.restartDnodes()
        tdSql.query("select 1-10 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(asct1,now)  from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") %s " % random.choice(self.unionall_or_union)
                sql += "select  ts , timediff(asct1,now)  from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts , (asct1),now(),today(),timezone()   from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") %s " % random.choice(self.unionall_or_union)
                sql += "select  ts , (asct2),now(),today(),timezone() from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  abs(asct1),now(),today(),timezone()   from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += " from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += ") %s " % random.choice(self.unionall_or_union)
                sql += "select  max(asct2),now(),today(),timezone() from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += "from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        #3 inter union not support
        tdSql.query("select 1-11 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(asct1,now), timediff(now,asct2) from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " %s "  % random.choice(self.unionall_or_union)
                sql += " select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts , asct1,now(),now(),asct2 from  ( select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " %s "  % random.choice(self.unionall_or_union)
                sql += " select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2, " % time_fun_2
                sql += "%s as asct121, " % random.choice(self.s_r_select)
                sql += "%s as asct122, " % random.choice(self.q_select)
                sql += "ts from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.order_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  asct1+asct2,now(),today(),timezone() from  ( select  "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += " from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += " %s "  % random.choice(self.unionall_or_union)
                sql += " select  "
                sql += "%s as asct1 ," % time_fun_1
                sql += "%s as asct2 " % time_fun_2
                sql += " from stable_2 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "order by asct1 "
                sql += "%s " % random.choice(self.limit1_where)
                sql += ")"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-12 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), timediff(asct1,now) from  ( select  t1.ts as ts1,"
                sql += "%s, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts2 ,timediff(ts1,ts2), asct1,now() from  ( select  t1.ts as ts1,"
                sql += "%s, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts as ts2  from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.order_u_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select min(floor(asct1)),now() from  ( select "
                sql += "%s as asct121, " % time_fun_join_1
                sql += "%s as asct1 " % time_fun_join_2
                sql += " from stable_1 t1 , stable_2 t2 where t1.ts = t2.ts and "
                sql += "%s " % random.choice(self.t_u_where)
                sql += "and %s " % random.choice(self.t_u_or_where)
                sql += "%s " % random.choice(self.limit1_where)
                sql += ");"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-13 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(%s,now)," % time_fun_2
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.q_select)
                sql += "%s as asct13, " % time_fun_2
                sql += "%s as asct122  " % random.choice(self.t_select)
                sql += " from  ( select  * from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts ,now(),today(),timezone(), "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct11, " % random.choice(self.q_select)
                sql += "%s as asct12, " % random.choice(self.q_select)
                sql += "%s as asct13, " % time_fun_2
                sql += "%s as asct122  " % random.choice(self.t_select)
                sql += " from  ( select  * from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                tdSql.checkRows(300)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  now(),today(),timezone(), "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct12  " % time_fun_2
                sql += " from  ( select  * from stable_1 where "
                sql += "%s " % random.choice(self.qt_where)
                sql += "%s " % random.choice(self.order_where)
                sql += ") ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-14 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts , timediff(asct1,now),timediff(now,asct2) from ( select ts ts ,"
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2" % time_fun_2
                sql += "  from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.partiton_where)
                sql += "%s " % random.choice(self.order_desc_where)
                sql += "%s " % random.choice([self.limit_where[2] , self.limit_where[3]] )
                sql += " ) ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts , (asct1),now(),(now()),asct2 from ( select ts ts ,"
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2" % time_fun_2
                sql += "  from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.partiton_where)
                sql += "%s " % random.choice(self.order_desc_where)
                sql += "%s " % random.choice([self.limit_where[2] , self.limit_where[3]] )
                sql += " ) ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select  (asct1)*asct2,now(),(now()) from ( select "
                sql += "%s as asct1, " % time_fun_1
                sql += "%s as asct2" % time_fun_2
                sql += "  from stable_1 where "
                sql += "%s " % random.choice(self.q_where)
                sql += "%s " % random.choice(self.partiton_where)
                sql += "%s " % random.choice([self.limit_where[2] , self.limit_where[3]] )
                sql += " ) ;"
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        tdSql.query("select 1-15 as time_nest from stable_1 limit 1;")
        for i in range(self.fornum):
            if (timelist == ['NOW','TODAY']) or (timelist == ['TIMETRUNCATE']) or (timelist == ['TO_ISO8601'])\
                or (timelist == ['TO_UNIXTIMESTAMP']) or (timelist == ['TIMEDIFF_1']) or (timelist == ['TIMEDIFF_2']):
                sql = "select  ts1,ts ,timediff(ts1,ts), timediff(asct1,now),timediff(now,asct2) from  ( select  t1.ts as ts1,"
                sql += "%s as asct2, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts  from stable_1  t1, stable_2 t2 where t1.ts = t2.ts and  "
                sql += "%s " % random.choice(self.t_join_where)
                sql += " and %s " % random.choice(self.qt_u_or_where)
                sql += "%s " % random.choice(self.partiton_where_j)
                sql += "%s " % random.choice(self.slimit1_where)
                sql += ") "
                sql += "%s " % random.choice(self.order_desc_where)
                sql += "%s ;" % random.choice(self.limit_u_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['TIMEZONE']) or (timelist == ['CAST']) or (timelist == ['CAST_1']) or (timelist == ['CAST_2']) or (timelist == ['CAST_3']) or (timelist == ['CAST_4']):
                sql = "select  ts1,ts ,timediff(ts1,ts), asct1,(now()),(now()),asct2 ,now(),today(),timezone() from  ( select  t1.ts as ts1,"
                sql += "%s as asct2, " % time_fun_join_1
                sql += "%s as asct1, " % time_fun_join_2
                sql += "t1.%s as asct11, " % random.choice(self.q_select)
                sql += "t1.%s as asct12, " % random.choice(self.q_select)
                sql += "t2.%s as asct13, " % random.choice(self.q_select)
                sql += "t2.%s as asct14, " % random.choice(self.q_select)
                sql += "t2.ts  from stable_1  t1, stable_2 t2 where t1.ts = t2.ts and  "
                sql += "%s " % random.choice(self.t_join_where)
                sql += " and %s " % random.choice(self.qt_u_or_where)
                sql += "%s " % random.choice(self.partiton_where_j)
                sql += "%s " % random.choice(self.slimit1_where)
                sql += ") "
                sql += "%s " % random.choice(self.order_desc_where)
                sql += "%s ;" % random.choice(self.limit_u_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)
            elif (timelist == ['ELAPSED']) :
                sql = "select   asct1,(now()),(now()),asct2 ,now(),today(),timezone() from  ( select "
                sql += "%s as asct2, " % time_fun_join_1
                sql += "%s as asct1 " % time_fun_join_2
                sql += "from stable_1  t1, stable_2 t2 where t1.ts = t2.ts "
                sql += "%s " % random.choice(self.partiton_where_j)
                sql += "%s " % random.choice(self.slimit1_where)
                sql += ") "
                sql += "%s ;" % random.choice(self.limit_u_where)
                tdLog.info(sql)
                tdLog.info(len(sql))
                tdSql.query(sql)
                self.cur1.execute(sql)
                self.explain_sql(sql)

        #taos -f sql
        startTime_taos_f = time.time()
        print("taos -f %s sql start!" %timelist)
        taos_cmd1 = "taos -f %s/%s.sql" % (self.testcasePath,self.testcaseFilename)
        #_ = subprocess.check_output(taos_cmd1, shell=True).decode("utf-8")
        _ = subprocess.check_output(taos_cmd1, shell=True)
        print("taos -f %s sql over!" %timelist)
        endTime_taos_f = time.time()
        print("taos_f total time %ds" % (endTime_taos_f - startTime_taos_f))

        print("=========%s====over=============" %timelist)


    def test_nestedQuery_time(self):
        """summary: xxx

        description: xxx

        Since: xxx

        Labels: xxx

        Jira: xxx

        Catalog:
            - xxx:xxx

        History:
            - xxx
            - xxx

        """

        tdSql.prepare()
        self.case_init()
        
        startTime = time.time() 

        # self.function_before_26()       
               
        # self.math_nest(['UNIQUE'])
        # self.math_nest(['MODE']) 
        # self.math_nest(['SAMPLE'])
                
        # self.math_nest(['ABS','SQRT'])     
        # self.math_nest(['SIN','COS','TAN','ASIN','ACOS','ATAN'])        
        # self.math_nest(['POW','LOG']) 
        # self.math_nest(['FLOOR','CEIL','ROUND']) 
        # self.math_nest(['MAVG'])  
        # self.math_nest(['HYPERLOGLOG']) 
        # self.math_nest(['TAIL']) 
        # self.math_nest(['CSUM'])
        # self.math_nest(['statecount','stateduration'])
        # self.math_nest(['HISTOGRAM']) 
        
        # self.str_nest(['LTRIM','RTRIM','LOWER','UPPER']) 
        # self.str_nest(['LENGTH','CHAR_LENGTH']) 
        # self.str_nest(['SUBSTR'])   
        # self.str_nest(['CONCAT']) 
        # self.str_nest(['CONCAT_WS']) 
        # self.time_nest(['CAST']) #放到time里起来弄
        # self.time_nest(['CAST_1'])
        # self.time_nest(['CAST_2'])
        # self.time_nest(['CAST_3'])
        # self.time_nest(['CAST_4'])

        self.time_nest(['NOW','TODAY']) 
        self.time_nest(['TIMEZONE']) 
        self.time_nest(['TIMETRUNCATE']) 
        self.time_nest(['TO_ISO8601'])
        self.time_nest(['TO_UNIXTIMESTAMP'])
        self.time_nest(['ELAPSED'])
        #self.time_nest(['TIMEDIFF_1'])
        #self.time_nest(['TIMEDIFF_2'])

        endTime = time.time()
        print("total time %ds" % (endTime - startTime))

        #tdSql.close()
        tdLog.success("%s successfully executed" % __file__)
